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 follow the standard sales formulas of YTD, and YTD for Prior year. Here are my formulas below:
SalesTotal = SUM('FactOrderDetails'[SalesAmt]) SalesTotalYTD = TOTALYTD([SalesTotal], DimDate[Date]) SalesTotalPreviousYTD = CALCULATE([SalesTotalYTD], SAMEPERIODLASTYEAR(DimDate[Date]))
The problem is that SalesTotalPreviousYTD forumla sums the entirety of last years sales, not the expected YTD. I initally suspected it was my Year slicer I had on the report but I removed it and the totals are still incorrect. I've tried a variety of other forumlas from around the web but they all seem to sum the entire previous year and this in theory should be fairly straight forward.
Are there any other areas I can spot check to make the forumla sum the data as expected? Follow up question, can I modify the forumlas above to play nice with a YEAR slicer on the report.
Thanks
Solved! Go to Solution.
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Thanks for the link Greg! I was able to get something working with the following code:
SalesTotalPreviousYTD = VAR __MaxYear = MAX('DimDate'[Year]) VAR __MaxMonth = MONTH(TODAY()) VAR __TmpTable = CALCULATETABLE( DimDate ,ALL('DimDate'[Year]),All('DimDate'[Month])) RETURN SUMX(FILTER(__TmpTable,[Year]=__MaxYear-1 && [Month] <= __MaxMonth),[SalesTotal])
While I'm happy I can start moving forward on my project this code is much harder to quickly read and might be difficult for anyone new to Power BI. Can you provide any insight on why I'm unable to use the built in functions to avoid this messy code? My DimDate table is marked as a date table, the TOTALYTD() function works as designed... I figure last years sales should be a just as easy.
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 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |