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

January

50

50

2018

A

XX

Services

February

65

115

2018

A

XX

Services

March

45

160

2018

B

YY

 Retail

January

21

21

2018

B

YY

 Retail

February

45

66

2018

B

YY

 Retail

March

23

89

2018

C

ZZ

Equipment

January

12

12

2018

C

ZZ

Equipment

February

55

67

2018

C

ZZ

Equipment

March

66

133

 

 

I can see how to do this in DAX but I am little confused as I am working with Text and not dates (I do also have a column called Month Number (1-12) per month which i might be able to use?

Is possible via DAX?

Thanks

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] )
    )
)

10 Replies

  • 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] )
    )
)