Forum Discussion
Persisting a Running Total
Anonymous , you can get running total like this using a date table
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
amitchandak Yes, the running totals are both calculating properly, as demonstrated in the table in both screen shots. There is also a date table marked properly and joined. The issue is that its two different series of data, so even though they're joined to the same date table, the series don't "persist forward" past their last date. Now I could go back in the date hierarchy to "month" or "quarter" or "year" but then the problem becomes that you still have to have data at that "scale". If one series only has data in January and the other series has data that carries forward to November and you only drill down to quarter, you would still have the same dilema. I want/need to be able to say value "X" (the running total) persists forward for all dates, even though there are no rows of data in the fact table driving that persistence.
I hope that makes sense.
Hi Anonymous ,
It's best to create a dummy .pbix file for test. Please remove sensitive information. It is suggested to upload your file to OneDrive for Business and then paste the link here.
Best Regards,
Icey
