Forum Discussion

ELW's avatar
ELW
Icon for Advocate II rankAdvocate II
4 years ago
Solved

Running Total with Date Slicers

I am trying to show a running total that is affected by a date range slicer.  It's all working as expected for the maximum date, but I'm having trouble getting the minimum date to filter correctly.  Here's a simplified example file 

 

The formula I'm trying to use is:  

Running Total = CALCULATE(
SUM('Table'[Sales]),
FILTER(ALL('Table'[Date]),
'Table'[Date]<=MAX('Table'[Date])
))
 
With an additional filter to base the minimum date on the slicer selection. 
 
So with my example data, I can get it working if I'm not setting a minimum date:
DateSalesRunning Total
1/1/202255
1/2/202249
1/3/2022413
1/4/2022417
1/5/2022522
1/6/2022830
1/7/2022333
1/8/2022639
1/9/2022443
 
But I want my results to look like the table below if I had the slicer set to a minimum date of 1/6/22, for example:
DateSalesRunning Total
1/6/202288
1/7/2022311
1/8/2022617
1/9/2022421

 

How can I modify my formula to do that?  Thanks in advance.

Need Help

6 Replies

    • ELW's avatar
      ELW
      Icon for Advocate II rankAdvocate II
      4 years ago

      Thank you!  Your solution appears to work perfectly so far!

    • bm_data's avatar
      bm_data
      Frequent Visitor
      4 years ago

      I downloaded the PBI and saw how you were able to get the data in a table and that is extremely helpful. I am trying to do something similar but with a card visualization. The slicer is a list of months and when I click on March, I would like to see the running total from the beginning of the year until the end of that month. I tried using TOTALYTD but was unable to get it to work properly with the slicer. I was only able to pull the total up to current time. 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        This measure should work

        =calculate(sum(Data[Amount]),datesbetween(Calendar[Date],date(year(min(calendar[Date])),1,1),max(Calendar[Date])))

        Ensure that you have a slicer for Month name and Year (both from the Calendar Table).  Select a year and month.