Forum Discussion

schoden's avatar
schoden
Post Partisan
6 years ago
Solved

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 E...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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]))

     

     

     

AllisonKennedy's avatar
AllisonKennedy
Community Champion
6 years ago
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's avatar
    schoden
    Post Partisan
    6 years ago

    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?

     
           
       
    • v-diye-msft's avatar
      v-diye-msft
      Community Support
      6 years ago

      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's avatar
        schoden
        Post Partisan
        6 years ago

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

    • AllisonKennedy's avatar
      AllisonKennedy
      Community Champion
      6 years ago
      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's avatar
        schoden
        Post Partisan
        6 years ago

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

        I have resolved this issue , Thanks