Decade Buckets in Power BI with DAX

Here is a quick DAX technique to create a decade buckets (or 10 year period groupings). I find this to be a useful calculated column to aide in visualizations to group decades of a year or decades of a life (age ranges).

Example below is for decade ranges, but this technique works well for ages groups as well, simply replace the Year column with a column representing age.

Calculated Column

=
VAR DecadeStart =
    ROUNDDOWN ( Date[Year], -1 )
VAR DecadeEnd = DecadeStart + 10
RETURN
    DecadeStart & " - " & DecadeEnd

Column inside calculated Date table

Date =
VAR FirstDayofCalendar =
    DATE ( YEAR ( TODAY () - 50 )11 )
VAR LastDayofCCalendar =
    DATE ( YEAR ( TODAY () )1231 )
VAR ThisYearCalendar =
    CALENDAR ( FirstDayofCalendarLastDayofCCalendar )
RETURN
    GENERATE (
        ThisYearCalendar,
        VAR YearDate =
            YEAR ( [Date] )
        VAR MonthNumber =
            MONTH ( [Date] )
        VAR DecadeStart =
            ROUNDDOWN ( YearDate-1 )
        VAR DecadeEnd = DecadeStart + 10
        RETURN
            ROW (
                "Year"YearDate,
                "Month Number"MonthNumber,
                "Month"FORMAT ( [Date], "mmmm" ),
                "Month Year"FORMAT ( [Date], "mmm yy" ),
                "Decade Bucket"DecadeStart & " - " & DecadeEnd
            )
    )

 

Once the column is created it can be used in visualizations. This is specifically helpful when used as a slicer or in the row/column of a bar chart as below.

Sales by Decade.PNG

Enjoy the article? Want to discuss services Datalore can offer you? Click the button below and send us an email!