Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Johanno
Responsive Resident
Responsive Resident

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

View solution in original post

4 REPLIES 4
Johanno
Responsive Resident
Responsive Resident

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])
Anonymous
Not applicable

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

Johanno
Responsive Resident
Responsive Resident

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

Anonymous
Not applicable

Thank you for your help! You are amazing! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.