Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Rolling Sum issue - help

Hi, can someone help me to calculate the Rolling Sum (the last column in the below example). I have Pro Id, Year-month, Total Bonus for each Year-month, and few other columns in the visual as shown below. I need a Rolling Sum of Bonus (last column) for each Pro Id as shown in example below.

 

Tried the following DAX measure, but it's not giving me expected results. Please advise.

Rolling Sum Bonus = CALCULATE('Mesures Table'[Total Bonus], FILTER(ALLSELECTED(Table1),Table1[Pro Id]<=MAX(Table1[Pro Id])),FILTER(ALL('Date Dim'),'Date Dim'[Year-Month Code] <= MAX('Date Dim'[Year-Month Code]) && 'Date Dim'[Year] <= MAX('Date Dim'[Year])))

 

Pro IdNameAccount NameProfile NameYear-MonthTotal BonusRolling Sum Bonus
2200720 ALAMOAGH DISTSep-211770717707
2200720 ALAMOAGH DISTOct-211897.519604.5
2200720ABC IncALAMOAGH DISTFeb-222307.3621911.86
2200720ABC IncALAMOAGH DISTApr-221129.323041.16
2200720ABC IncALAMOAGH DISTJun-228806.731847.86
2200720ABC IncALAMOAGH DISTJul-2271332560.86
2200720ABC IncALAMOAGH DISTAug-22607.233168.06
2379628ABC IncXYZAGI IncOct-21638.94638.94
2379628ABC IncXYZAGI IncMar-2214262064.94
2379628ABC IncXYZAGI IncJun-22922156.94
2379628ABC IncXYZAGI IncJul-22577.32734.24
rolling sum
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please try the below measure that I amended a little bit, and please check the attached file.

     

    Rolling sum measure: =
IF (
    NOT ISBLANK ( [Total bonus measure:] ) && HASONEVALUE ( Data[Pro Id] ),
    CALCULATE (
        [Total bonus measure:],
        FILTER ( ALL ( Data ), Data[Pro Id] = MAX ( Data[Pro Id] ) ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

     

  • Anonymous's avatar
    Anonymous
    3 years ago

    Ecellent. It worked. Thank you!

     

    Appreciated.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Ecellent. It worked. Thank you!

     

    Appreciated.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not quite sure if I understood your question correctly, but I assume you want to NOT show the rolling total result if the bonus is empty.
    Please check the below picture and the attached pbix file.

    I tried to create a sample pbix file that the datamodel looks like below.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

     

    Rolling sum measure: =
IF (
    NOT ISBLANK ( [Total bonus measure:] ),
    CALCULATE (
        [Total bonus measure:],
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, Thanks for the code. But, the rolling sum messed up as soon as I added other columns, Account Name and Name. please see below, starting row 3 it started the new rolling sum, as this is part of same Pro Id, the rollowing should continue from Top.

      please advise. Appreciate your help.

       

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        Please try the below measure that I amended a little bit, and please check the attached file.

         

        Rolling sum measure: =
IF (
    NOT ISBLANK ( [Total bonus measure:] ) && HASONEVALUE ( Data[Pro Id] ),
    CALCULATE (
        [Total bonus measure:],
        FILTER ( ALL ( Data ), Data[Pro Id] = MAX ( Data[Pro Id] ) ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)