Running Totals in DAX

[Anonymous]

Hi All -

I've tried all suggestions on running totals using DAX.  I haven't had any luck though.

Can you sum a measure?  

Call Counts = COUNTROWS(CallTable)

I also tried creating a custom Calculated Column to see if that would work but both columns are still the same.  It's not accumulating.  

Running Total Test = 

CALCULATE (
SUM ( CallTable[Call Count Col] ),
FILTER (
ALL ( dimDate[Date] ),
dimDate[Date] <= MAX ( dimDate[Date] )
)
)

Also, tried - 

Running Total Test2 = IF(MIN(DimDate[Datekey])<=CALCULATE(MAX(CallTable[DateKey]),ALL(CallTable)),CALCULATE([Call Count],FILTER(All(DimDate[Datekey]),DimDate[Datekey]<=MAX((DimDate[Datekey])))),BLANK())

Ultimately, need to running total a Call count column which is a measure.  I can't seem to SUM a measure.  Any help is greatly appreciated!  Thank you.  Using a Matrix for testing purposes; the Row ='s Customer Name; Column = Month (1-12); Values = Call Count and Running total.  If I can get the running totals properly I will just use that column.

[Anonymous]

I found my solution!  Used COUNT instead of SUM.  

[Anonymous]

I have a matrix by Month, Customer Ranking and counts, what can I do to fill the blank rows with 0.  I've tried ISBLANK however that didn't work.  Also, need to always show months 1-12.

            Month    1   2   3   4   5   6   7   8   9   10   11   12     

Customer Rank

1                        1   1        1    2   2   2   1  3    4     5      1

2                        1   1        1    2   2   2   1  3    4     5      1

3                        1   1        1    2   2   2   1  3    4     5      1

4                        1   1        1    2   2   2   1  3    4     5      1

Need 0's for the Month #3.

Thank you.

[Anonymous]

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 time, or for inventory models the total on hand at a given time.  You can find more tips and tricks at my blog, www.bipatterns.com.

Let’s start with a base measure in a very simple pivot table.
Total Sales :=
CALCULATE ( SUM ( FactSales[SalesAmount] ) )

Now lets take our first attempt at computing a running total.  This is the most intuitive formula, but it has one common pitfall that isn’t necessarily easy to see right away.
Cumulative Total Sales :=
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( DimDate[Datekey] ),
        DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
    )
)
Key parts of the Formula: The use of ALL(DimDate[DateKey]) results in the current context being ignored, so dates outside of the current pivot row context will be analyzed.  The second key step is the comparison of DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ).  This means that all dates in the DateKey column that are before the current pivot table row context will be calculated.

If we put this measure on a table, we’ll get the correct numbers but we will have one issue remaining.

The formula returns a number for dates that have no sales.  We need to add some error handling, which is outlined below.
Cumulative Sales (Correct) :=
IF (
    COUNTROWS ( FactSales ) > 0,
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( DimDate[Datekey] ),
            DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
        )
    ),
    BLANK ()
)

The IF Function checks to make sure that there are sales in the current selected context, otherwise returning blank.  You can see the difference between the two measures below:

If you have any questions for me, you can reach me via LinkedIn or in the PowerBI Community.

[Anonymous]

This solution has gotten me closer.  Very close.  Instead of using Blank() I had used 0.  However, I'm thinking I would need Month = 3 for Customer Rank = 1 to be 2.  Not sure to add or somehow have that Cumulative count represent when it's blank for that Month.

Month               1   2   3   4   5   6    7    8     9   10    11     12     

Customer Rank

1                        1   2        3    5   7   9   10   13   17    22     23

2                        1   2        3    5   7   9   10   13   17    22     23

3                        1   2        3    5   7   9   10   13   17    22     23

4                        1   2        3    5   7   9   10   13   17    22     23

Thank you!!

[Greg_Deckler]

If Call Counts is a measure, then you shouldn't need the SUM, just CALCULATE([Call Counts],FILTER...)