Subtracting from a previous row calculation

[Anonymous]

Hi, 

I am trying to calcute a projection for next year. How this is calculated is the total Products is subrtracted from current improvemnts and the new value calulated will be the new total products. However currently my table is always subtracting from 2603 rather than updating with the new value. 

Goal: 

Total products = 2603 

Current Improvements = 25 

2603 - 25 = 2578 

New Value total Prouct = 2578 

2578 - Next value for current improvements

and so on.. 

[v-yanjiang-msft]

Hi @Anonymous ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, if you only want to get value in the previous row, simply add an index column in Power Query. Or if you want to get value of the previous quarter, firstly create a calculated column.

Rank =
RANKX ( 'Table', 'Table'[Year] & 'Table'[Quarter],, ASC )

 It will return a rank according to the year and quarter.

Then create another calculated column.

Column =
MAXX (
    FILTER ( 'Table', 'Table'[Rank] = EARLIER ( 'Table'[Rank] ) - 1 ),
    'Table'[Current Improvements Graph]
) - 'Table'[Current Improvement1]

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi, 

Thank you very much for your help. However, I am gettng an error when creating the rank. I take the year and the quarter from the created date of my data. 

[v-yanjiang-msft]

Hi @Anonymous ,

From the error message, do you create a measure by the code? Can you create a calculated column by the code.

Additionally, this is a visual, is it the same format in the data table.

Best Regards,
Community Support Team _ kalyj