Search code examples
powerbidaxdata-warehouse

Rounding months to the correct year

I am creating an Age Dimension. Granularity is months, so i have started by creating a column called TotalMonths, between 1 - 1440, which equates to 120 years.

I now want to add a year column. I thought totalMonths/12 would work, but on month 6, the year rounds up to 1. The year should only round up after 12.

Is there a way to do this in dax?

Solution

  • If you want to make a custom column in Power Query, then you can use Number.RoundDown function:

    Year_M = Number.RoundDown([TotalMonths] / 12)

    If you do it in DAX, then use ROUNDDOWN function:

    Year_DAX = ROUNDDOWN(Age[TotalMonths] / 12, 0)

    enter image description here

    If you want first Year=1 to be for TotalMonth=13 instead, then subtract 1 from TotalMonth in the above calculations:

    Year_M = Number.RoundDown(([TotalMonths] - 1) / 12)
Year_DAX = ROUNDDOWN((Age[TotalMonths] - 1) / 12, 0)

    enter image description here