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 b...
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.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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] )
    )
)