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/