cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ogureisuo Regular Visitor
Regular Visitor

Help: Inverse Cumulative

Hello Everyone,

 

I only have the cumulative value. I need to get the total amount.

Can anybody help me inverse the cumulative amount, please?? 

 

 

Year                       Month                                              Cumulative                Total

2017  January18    18
2017February28 
2017March52 
2017April68 
2017May105 
2017June137 
2017July166 
2017August192 
2017September214 
2017October242 
2017November280 
2017December313 
2018January29 29
2018February52 
2018March72 
2018April91 
2018May115 
2018June137 
2018July171 
2018August205 
2018September252 
2018October334 
2018November381 
2018December419 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Johanno Member
Member

Re: Help: Inverse Cumulative

Ok, than add an if statement in the calculated column to start over in january:

Previousrow = 
VAR previousrow = CALCULATE(
                    SUM(Table1[Cumulative]);
                    FILTER(Table1;Table1[Index]=EARLIER(Table1[Index])-1)
)
RETURN
    IF(Table1[Month]="January";0;previousrow)

Capture.JPG

4 REPLIES 4
Johanno Member
Member

Re: Help: Inverse Cumulative

This might not be the best solution, but you could (with misspellings):

1. Create an index column in Power Query 1,2,3 etc.

Capture.JPG

2. Create a calculated column with the previous row:

Previousrow = 
VAR previousrow = CALCULATE(
                    SUM(Table1[Cumulative]);
                    FILTER(Table1;Table1[Index]=EARLIER(Table1[Index])-1)
)
RETURN
    previousrow

3. Create a measure that takes

Total = SUM(Table1[Cumulative])-SUM(Table1[Previousrow])
ogureisuo Regular Visitor
Regular Visitor

Re: Help: Inverse Cumulative

Thank you for your solution however in my case, the cumulative always starts in January.

Highlighted
Johanno Member
Member

Re: Help: Inverse Cumulative

Ok, than add an if statement in the calculated column to start over in january:

Previousrow = 
VAR previousrow = CALCULATE(
                    SUM(Table1[Cumulative]);
                    FILTER(Table1;Table1[Index]=EARLIER(Table1[Index])-1)
)
RETURN
    IF(Table1[Month]="January";0;previousrow)

Capture.JPG

ogureisuo Regular Visitor
Regular Visitor

Re: Help: Inverse Cumulative

Thank you for your help! You are amazing!