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)))

Calculating Previous Month total with Time Intelligence Function

Have you ever come across calculating previous month’s sales? It’s fascinating to learn how time intelligence function works.

I have come across two easy way to write the code to get the total sale of previous month. Either you can use the DATEADD() or PREVIOUSMONTH() functions to get the total sale from Total Sale measure.

Previous Month Sale = CALCULATE([Total Sale],DATEADD(dimDate[FullDate],-1,MONTH))

Previous Month Sale = CALCULATE([Total Sale],PREVIOUSMONTH(dimDate[FullDate]))

What I’m trying to explain here is when I used this DAX commands above measures, it ended up giving me blanks. Let me explain you how I resolved this issue and how I make it working.

Thanks to https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/ post, I added a dummy fact table into my model.

dummyTable =

FILTER (

    DATATABLE( "Date", DATETIME, { { BLANK() } } ),

    FALSE

)

According to the post I created 1:M cardinal in single cross filter direction(dimDate(1) to dummyTable(*)) as explained in the post.

Amazingly, my “Previous Month Sale” end up showing me the expected values.

Thanks Marco for this amazing article https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

Convert Date from Integer to Date in DAX

I have a dimension table that contains only the “YearMonth” key. Now my target is to create the first date for each YearMonth key. I simply get this by concatenating it with 01 and change the date type into Whole Number.

DateIntColumn = CONCATENATE('Month'[MonthKey],"01")

My next target is what the reason I’m writing this post. How I’m going to get the date type filed out from this date number. What I do is I simply break it to ‘yyyy,mm,dd’. Below is the DAX I have used.

Column = DATE(INT(LEFT([DateIntColumn],4)),INT(MID([DateIntColumn],5,2)),INT(RIGHT([DateIntColumn],2)))