PREVIOUSMONTH('DateTime'[DateKey])
Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 at this for hours searching through this community and youtube and nothing is working! Please help!
Solved! Go to Solution.
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.
Proud to be a Super User!
@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-Y...
https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...
Appreciate your Kudos.
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.
Proud to be a Super User!
I had the same issue. It was solved by marking my Calendar table as the date table:
I have my date table connected to the table with my values. Or did I do this incorrectly?
Maybe you can try
DAX
=CALCULATE(SUM(amount)), PREVIOUSMONTH('Time Map'[Date]))
Proud to be a Super User!
You are pulling date from BPC table in your measure, you should be using date from "Time Map" table (if this is your date table)
Proud to be a Super User!
Do you create a calendar table? You need to build the relatonship between your current table and calendar table
Please see the previousmonth function below.
PREVIOUSMONTH('DateTime'[DateKey])
https://docs.microsoft.com/en-us/dax/previousmonth-function-dax
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |