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.
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
Solved! Go to 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...
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
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))
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. 🤔
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
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...
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
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |