There seems to be a fitting pattern: https://www.daxpatterns.com/cumulative-total/
Date Range Dynamic - Grouped Running Total
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.
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.
Sign in to comment
Sort by: Most helpful
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.
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.