Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Subtracting from a previous row calculation

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.. 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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