Compare Value Against Previous Month Value

[PaulMac]

Hello All

I have a query that need some assistance with.

A report is produced produced on Feb 1st 2020 (01/02/2020) showing data for January 2020 (aka the previous month) I need to compare January 2020 values against December 2019 values. Can this be done in February 2020 using the PREVIOUSMONTH DAX function?

Many thanks

Paul Mac

[amitchandak]

I am assuming you have date table, Use datesmtd or totalmtd or trailing measure

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

last to last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-2,MONTH))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[amitchandak]

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

2 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Month))

[PaulMac]

Many thanks for the swift reply @amitchandak 

I don't appear to be getting the result I was expecting.

2 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Month))

Would your example above work if the section 

CALCULATE(SUM(Sales[Sales Amount])

 was an exisiting dax measure. So in my example it would be:

2 Month behind Sales = CALCULATE([Total Phone Work Per FTE],dateadd('Date'[Date],-2,Month))

But this gives me a value that is incorrect. I get a result of 994 when the December value is actually 660.23. 🤔

[PaulMac]

phonesdata.csv Dummy Data 

I need to create a measure that will output December's Value on a Card in Power BI to compare against January's data in a report produced in early February

[amitchandak · Accepted solution]

Try this one too

last to last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-2,MONTH))))

If does not work

If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[PaulMac]

Yep! This was the winner! 😁

Test 2 month behind = CALCULATE([Total Phone Work Per FTE],DATESMTD(ENDOFMONTH(DATEADD('Date'[date],-1,MONTH))))

Although to get my december value I changed -2 to -1 as -2 was giving November's value! 

Many thanks again @amitchandak YTM