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 need to show the previous months sales broken down by day. So last month on this day total sales was X.
Problem is the DAX calculate function is agregating sales of the entire month together, which isnt what I want.
Left one is showing correct when drilled by month, Right one is incorrect when drilled into day ( should show the sales amount for the same day last month)
DAX calc used
PreviousMonthSales = CALCULATE(SUM(Sales[TotalRev]),PREVIOUSMONTH('Date'[Date]))
I couldn't find any similar question asked in the past, which I thought was quite odd. ANy Help is appreciated,
Solved! Go to Solution.
Try using the DATEADD() function.
=CALCULATE( [Total Sales], DATEADD('Calendar'[Date],-1,MONTH) )
It won't aggregate the previous period.
The problem is if the previous month doesn't have that day, it will repeat the last day of the month. So, if you are using that measure for March 29, 30, and 31, they will all report Feb 28. You would need to add some IF() logic to it to mask that if it is an issue.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingTry using the DATEADD() function.
=CALCULATE( [Total Sales], DATEADD('Calendar'[Date],-1,MONTH) )
It won't aggregate the previous period.
The problem is if the previous month doesn't have that day, it will repeat the last day of the month. So, if you are using that measure for March 29, 30, and 31, they will all report Feb 28. You would need to add some IF() logic to it to mask that if it is an issue.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |