Forum Discussion
Running Total
Hi Experts,
I have been spending last 2 hours on this formula and it doesnt seem to work still. Please help.
I needed a running Total for the actual Hours from Many sided Table( With date Entered, actual hour accumulates). The two tables are have one to many relationship. The V_PM_project table filters the many sided table.
Hi schoden
Try this one?
Running total = calculate(sum(V_PM_project(Hours_actual),Filter(ALLSELECTED(V_PM_project),[date_entered]<=max(V_PM_project'[date_entered]))
8 Replies
- You must have some other filters on the table in your screenshot, or other slicers on the page? These will be affecting what is shown in the Hours_Actual column of the table, but because you are using the ALL() DAX function, that slicer/filter isn't being accounted for in the measure. Try removing the ALL() from the measure and using DATESINPERIOD instead of the <= for your date filter. You will need a DimDate table for this to work as expected: https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html
Hi AllisonKennedy Thanks for getting back ... You explained very well it to why ALL will not work here as I have other filters applied 🙂
I tried with date table too , since it didnt pop up any dates , so I went with orginal table dates , here it
is Date_entered.
But I am confused what shall I put in the interval arguments if I am to use DATESINPERIOD?
Hi schoden
Try this one?
Running total = calculate(sum(V_PM_project(Hours_actual),Filter(ALLSELECTED(V_PM_project),[date_entered]<=max(V_PM_project'[date_entered]))
schoden , Use a separate date table in such cases
example
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date]))) Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[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.Hi amitchandak ,
Thanks for getting back to me. I made date table and marked as date table ...which is related to date_entered on the table that has the actual hours. The result is shown in the picture;
