Forum Discussion

Helper II

6 years ago

Solved

Help! Previous Month Function is not working!!

I'm trying to pull in the previous month sales, but I'm getting a blank. It's working perfectly fine for the current month actuals. I'm referring to dates and it still isn't working! I've been...

FarhanAhmed
6 years ago
You need to create Date Calendar and join it with your current table. Time Intelligence function only work with Date Table.
Even your MTD function will not work if you put Date Values in Table. Since you are showing Location thats why it is giving sum of location for that month. but if you put date column in Matrix/Table you will see that MTD will not work as expected.

Super User

6 years ago

jaynesa , Please create a date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('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))))

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885

Appreciate your Kudos.

Related content