Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Cumulative value to Periodic value

Hello

 

I am trying to find a way to create a column in my table that takes the cummulative value column and calculates the difference between each month (variance). My data looks like this and is based on monthly data extracts: 

 

Need Help
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column:

    Cumulative Sum = 
CALCULATE ( SUM ( 'Table'[Value] ),
    FILTER ( 'Table',
        [Date] <= EARLIER ( 'Table'[Date] )
            && [Month] = EARLIER ( 'Table'[Month] )
            && [Year] = EARLIER ( 'Table'[Year] )
    )
)
    Preivious sum = MAXX(FILTER('Table',[Month]=EARLIER('Table'[Month])-1),[Cumulative Sum])  
    Difference = 
IF ([Preivious sum] = BLANK (),
    BLANK (),
    IF ([Cumulative Sum] = MAX ( 'Table'[Cumulative Sum] ),
        [Cumulative Sum] - [Preivious sum],
        BLANK ()
    )
)

    Hope this method helps you.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Additional info: the contact time charge column is the cummulative field

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column:

    Cumulative Sum = 
CALCULATE ( SUM ( 'Table'[Value] ),
    FILTER ( 'Table',
        [Date] <= EARLIER ( 'Table'[Date] )
            && [Month] = EARLIER ( 'Table'[Month] )
            && [Year] = EARLIER ( 'Table'[Year] )
    )
)
    Preivious sum = MAXX(FILTER('Table',[Month]=EARLIER('Table'[Month])-1),[Cumulative Sum])  
    Difference = 
IF ([Preivious sum] = BLANK (),
    BLANK (),
    IF ([Cumulative Sum] = MAX ( 'Table'[Cumulative Sum] ),
        [Cumulative Sum] - [Preivious sum],
        BLANK ()
    )
)

    Hope this method helps you.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    This method unfortunately did not work v-zhangti 

     

    I am trying to replicate the "sum of allocated fee movement" column here (excel formula driven) with the following: 

     

    Sum of Allocated Fee (Month 11) - sum of allocated fee (month 10) = 8,035,357 in the Sum of allocated fee movement column for Month 11

     

    I'd need to replicate this calculation for each month and even do January current year less December previous year. The value is cummulative since inception of a project, so can go back many many years.