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
jthomson's avatar
jthomson
Icon for Solution Sage rankSolution 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
      Icon for Community Champion rankCommunity 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!