Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Rolling sum

Hi,   I am looking at creating a total sum per month as below in the Running Total. Year Region Cat Category MONTH Savings Running Total 2018 A XX Services ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    Try this as a MEASURE

     

    Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ),
        Targets[Date] <= SELECTEDVALUE ( Targets[Date] )
    )
)

     

    or this as a CALCULATED COLUMN

     

    Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ),
        Targets[Date] <= EARLIER ( Targets[Date] )
    )
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    I think that whatever Column you use as a slicer would need to be part of ALLEXCEPT function. For example if you use region as a SLICER

     

    Then Measure would be

     

    Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ,Targets[Region]),
        Targets[Date] <= SELECTEDVALUE ( Targets[Date] )
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Anonymous

 

I think that whatever Column you use as a slicer would need to be part of ALLEXCEPT function. For example if you use region as a SLICER

 

Then Measure would be

 

Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ,Targets[Region]),
        Targets[Date] <= SELECTEDVALUE ( Targets[Date] )
    )
)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Great just updated my report and that dose seem to be the case thanks for all of your help