Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Cumulative (Running) Totals in DAX

A common Measure that you’ll probably find useful in PowerPivot or SSAS Tabular Models is finding running totals.  For example, you may want to see total sales of a product as it accumulates over tim...
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    10 years ago

    sdjensen

     

    Give the following a try:

    Cumulative Sales:=IF(MIN(DimDate[Datekey])<=CALCULATE(MAX(FactSales[DateKey]),ALL(FactSales)),CALCULATE([Total Sales],FILTER(All(DimDate[Datekey]),DimDate[Datekey]<=MAX((DimDate[Datekey])))),BLANK())

     

    Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

     

    Thanks,

    Ryan Durkin

sdjensen's avatar
sdjensen
Icon for Solution Sage rankSolution Sage
10 years ago

Do you have an alternative solution instead of using IF ( COUNTROWS(FactSales) > 0? Lets say you drill down to day level and some days don't have any rows in your fact then a value will not be calculated for these days.

 

IF (
    COUNTROWS ( FactSales ) > 0,
...
...
...
    BLANK()
)
Anonymous's avatar
Anonymous
Not applicable
10 years ago

sdjensen

 

Give the following a try:

Cumulative Sales:=IF(MIN(DimDate[Datekey])<=CALCULATE(MAX(FactSales[DateKey]),ALL(FactSales)),CALCULATE([Total Sales],FILTER(All(DimDate[Datekey]),DimDate[Datekey]<=MAX((DimDate[Datekey])))),BLANK())

 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

 

Thanks,

Ryan Durkin

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    This suggestion has gotten me closer.  Thank you!

    Instead of Blank() I used 0, however, since it's a cumulative count I'm going for, I'm thinking instead of zero I would actually need the value show before.  So that 0 plus the last value.  Any ideas on getting that for the true part of this IF statement?

     

    Cumulative Sales (Correct) :=
    IF (
        COUNTROWS ( FactSales ) > 0,
        CALCULATE (
            [Total Sales],
            FILTER (
                ALL ( DimDate[Datekey] ),
                DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
            )
        ),
        BLANK ()
    )