Forum Discussion
DAX Question on YTD Totals
- 9 years ago
Solution was listed here at the bottom: https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/
If you are using a dimDate , then joining on Integer Date will not "Mark the Date Dim as Date Dim" idea. You have to create a dummy date table as illustrated towards the middle of that article and setup a join between it and the date dim. This basically "tricks" the model into thinking that the Date field (not dimDayId) is the primary key.
It's sort of a workaround, but it gets it done.
For cumulative totals by month the formulas ended up being:
Local Periodic Balance Amount (measure in the table) this just gives the monthly total
Local Period Balance Amt Cumulative = TOTALYTD(sum(factGLBalance[Local Periodic Balance Amt]),'prd dimDate'[Date],DATESYTD('prd dimDate'[Date]))
Local Period Balance Amt PY Cumulative = TOTALYTD(sum(factGLBalance[Local Periodic Balance Amt]),SAMEPERIODLASTYEAR('prd dimDate'[Date]))
Local Period Cumlative YoY Variance = [Local Period Balance Amt Cumulative] - [Local Period Balance Amt PY Cumulative]
Local Period YoY Variance % = DIVIDE( [Local Period Cumlative YoY Variance] ,[Local Period Balance Amt PY Cumulative])
Hope that helps anyone having similar issue!
hey,
let me direct your attention to this site:
http://www.daxpatterns.com/time-patterns/
hope this helps
Thanks Tom, but I have looked at at that example, It is pretty similar to what I have posted and still no dice.
- mattbrice9 years ago
Solution Sage
Actually, Tom's code is not that similar to what you initially posted and from what info you have provided his solution should work. If it does not, then I would check to make sure you have don't have other filters causing issues.