Date Range Dynamic - Grouped Running Total

Question

I'm trying to get a dynamic running total for unique items based on a date slicer I have in my report. I have multiple Item Id's with quantities for each month, and I can get a running total form them in a calculated column using this formula:

CALCULATE(SUM(Table[Qty]), FILTER(Table, Table[Item ID]=EARLIER(Table[Item ID]) && Table[Month]<=EARLIER(Table[Month])))

However, in my report the user will have the ability to change a date slicer so the entire set of data is no longer there. I assume I then need to use a measure instead of a column with some type of AllSelected method. I'm not sure how to do this however with my Item Id grouping.

Expected results:

As the user changes the date range I want the result to change with it. The "Running Total" column is what I'm looking to get. The final column is what I currently have through a calculated column. As you can see it works with the entire set of data, but not when the user shrinks the date range.

Answer 1

There seems to be a fitting pattern: https://www.daxpatterns.com/cumulative-total/

Answer 2

Hi @Jacob ,

Thanks for your posting.

Please refer to below DAX:

Monthly Running Total:=   
IF(  
    [Internet Total Sales] <> BLANK(),  
    CALCULATE(  
        [Internet Total Sales],  
        FILTER(  
            ALL('Date'[Date]),  
            'Date'[Date] <= MAX('Date'[Date])  
        )  
    )  
)  

For more information, please refer to Running Total in Power BI.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.