Forum Discussion

v-amarsh's avatar
v-amarsh
Microsoft Employee
6 years ago

Cumulative sum

Hi Community, I am trying to get a running total on a field based on condition on a different column. Below is my input table along with expected output column (Cumulative Assigned)to produce . Col...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-amarsh,

I can't access your link, can you please upload the sample to 'onedrive for business' and share the link here?
Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-amarsh,

    I test the modified formula and it can output the expected result as you shared, please try it if it meets your requirement:

    Cumulative Table = 
SUMMARIZE (
    'Role Master',
    'Role Master'[GD SI Role],
    'Role Master'[Resource  Plan],
    'Role Master'[GSI Name],
    'Role Master'[Fiscal Year],
    'Role Master'[Month],
    'Role Master'[Assigned],
    "MyExpectedOutputColumn",
    VAR maxMonth =
        CALCULATE (
            MAX ( 'Role Master'[Month] ),
            FILTER ( ALLSELECTED ( 'Role Master' ), [Assigned] <> BLANK () ),
            VALUES ( 'Role Master'[Fiscal Year] ),
            VALUES ( 'Role Master'[GSI Name] ),
            VALUES ( 'Role Master'[GD SI Role] )
        )
    RETURN
        IF (
            RIGHT ( [Month], 2 ) < RIGHT ( maxMonth, 2 ),
            CALCULATE (
                SUM ( 'Role Master'[Assigned] ),
                FILTER (
                    ALLSELECTED ( 'Role Master' ),
                    RIGHT ( [Month], 2 ) <= RIGHT ( EARLIER ( 'Role Master'[Month] ), 2 )
                ),
                VALUES ( 'Role Master'[Fiscal Year] ),
                VALUES ( 'Role Master'[GSI Name] ),
                VALUES ( 'Role Master'[GD SI Role] )
            )
        )
)

    Regards,

    Xiaoxin Sheng