Date Range Dynamic - Grouped Running Total

Jacob 61 Reputation points
2021-09-17T19:31:39.343+00:00

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
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,256 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,416 Reputation points
    2021-09-18T16:52:31.873+00:00

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

    0 comments No comments

  2. CarrinWu-MSFT 6,866 Reputation points
    2021-09-20T07:19:00.89+00:00

    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.

    0 comments No comments