Forum Discussion

klod78's avatar
klod78
Frequent Visitor
6 years ago
Solved

Running Total

I am trying to calculate the running total, but it is not working as I want. If you look at the pictures below, it works if I don't apply any filter, but if for instance I want to see the data for 2018, I would expect to see under running total in february 2018 the number 3 and in correspondence of march 2018 the number 4...the sum of february and march...but I see always the number 8 and 9...what is wrong? the formula I used is:

Running total =
CALCULATE (
SUM('Sheet1 (2)'[Count]);
FILTER(
ALL('Sheet1 (2)' );
'Sheet1 (2)'[IntrDate] <= MAX('Sheet1 (2)'[IntrDate])
)
)

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    klod78 , Try like

    Running total =
    CALCULATE (
    SUM('Sheet1 (2)'[Count]);
    FILTER(
    ALLselected('Sheet1 (2)' );
    'Sheet1 (2)'[IntrDate] <= MAX('Sheet1 (2)'[IntrDate])
    )
    )

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    Here is a running total pattern:

     

    Running Total = 
VAR MaxDateInFilterContext =
    MAX ( Dates[Date] )
VAR MaxYear =
    YEAR ( MaxDateInFilterContext )
VAR DatesLessThanMaxDate =
    FILTER (
        ALL ( Dates ),
        Dates[Date] <= MaxDateInFilterContext
            && Dates[Calendar Year Number] = MaxYear
    )
VAR Result =
    CALCULATE (
        [Total Sales],
        DatesLessThanMaxDate
    )
RETURN
    Result