Tuesday, 4 June 2019

Create previous YearMonth using DAX


In one of my Power BI report I had to use previous Month and Year and total sales against each.

Current Year Month
Previous Year Month
201901
201812
201902
201901
201903
201902

In DAX I can build this measure very easily. Below is the DAX command I created to display the Previous Year Month column.

PreviousYearMonth =
var curmonth=  IF (
        MONTH ( min(dimdate[date])) = 1,
        12,
        MONTH ( min(dimdate[date]))-1
    )

Var curryear = if(MONTH ( min(dimdate[date])) = 1,
        year( min(dimdate[date])) -1,
        year(MIN(dimdate[date])
   ))
return CONCATENATE(curryear,if(curmonth>=10,curmonth,CONCATENATE(0,curmonth)))

No comments:

Post a Comment