Running Total

[schoden]

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.

[amitchandak]

@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-Y...


Appreciate your Kudos.

[schoden]

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; 

[AllisonKennedy]

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

[schoden]

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?

[AllisonKennedy]

If you use DATESINPERIOD you would do something like -3, YEAR) for the number of intervals and interval, depending on how far you want the running total to go back in time.

Another option could be to use DATESBETWEEN() and just provide start and finish dates

[schoden]

Thank You for sharing your knowledge .... As a new Power BI Learner , its very helpful 😄 

I have resolved this issue , Thanks 

[v-diye-msft · Accepted solution]

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]

@v-diye-msftThanks a lot ...It worked for me with your DAX.... ALL and ALLSELECTED Difference 😄  Fabulous 😄