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] )
    )
)
jthomson's avatar
jthomson
Solution Sage
8 years ago

I'd try to create a dummy date field from the information you have - calculate a column through DATE ([Year], [whateveryourmonthnumbercolumniscalled],1) which should create a field with the first day of the month in question

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Great i have made that but when i put the Dax in to the new mesure it does not roll up per month;

My dax i have used is below;

 

Saving Roll =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALL ( Targets[Date] ),
        Targets[Date] <= MAX ( Targets[Date] )
    )
)

 

Wonder what could be going wrong?

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    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] )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks that works, starting the under stand Dax better now!

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Arr thought it was 100% but this woks if the slicer is Catrgory but not if it is any of the others slicers :(

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Anonymous

         

        When I use your data and use Region as slicer...It seemingly works well.

        What slicer did you use?