question

Jacob-8126 avatar image
0 Votes"
Jacob-8126 asked CarrinWu-MSFT answered

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.

Expected results:
133233-image.png

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.


sql-server-analysis-services
image.png (22.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @Jacob-8126,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.