Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Measure use previous row

Hi all, 

 

I have a DAX measure I'm stuck with for a few days now. 

The idea is to get the "Result" as a DAX measure in PBI. 

Here's the equivalent in Excel : 

  • Result = [@[Previous Result]]*[@Rate]+[@X]

I encouter circular references with PBI, as the Previous Resultreferences the result, and result references the Previous Result.

 

In my screenshot, there are 4 rows, but I could have much more. 

 

Any idea how to solve this ? 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ，

     

    So you wanna calculate the value  of “result” by column “Number ”,”x”  and “Rate”,right?

    I have modified the measure,pls see below:

    Measure = MAX('Table'[Previous Result])*MAX('Table'[Rate])+MAX('Table'[X])

    You can also create a calculated column,which is as below:

     

    Column = 'Table'[Previous Result]*'Table'[Rate]+'Table'[X]

     

    With both ways above,you will finally see as below:

     

     

    For the related .pbix file,pls click here.

     

    Hope this would help.

     

    Best Regards,

    Kelly

     

4 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    What you need to do is to create 3 calculated columns:

    1 An index column using query editor, “add column”->”Index column”->”from 1”,then you will see as below:

     

    2.Create a column which calculates the previous result based on the value of “Result”:

    Select “calculated column”:

     

     

    dax PreviousResult = 
var a ='Table'[Result ]
var b ='Table'[Index]
Return
CALCULATE(SUM('Table'[Result ]),FILTER('Table',[index]=b-1))

     

     

    3. Create a column which calculates result based the value of previous result:

     

    dax Result = 'Table'[dax PreviousResult]*'Table'[Rate]+'Table'[X]

     

     

    Then you will find see as below:

     

     

    For the related .pbix file ,you can turn to URL:https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-kellya_microsoft_com/EUhsDoRxQkREss9Xo4HawyoBZgEmWWggDsHOiXUYYJ6wQA?e=VSeWhu

     

    Hope this would help.

     

    Best Regards,

    Kelly

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, 

       

      This doesn't work, as you're using the result itself in the "dax PreviousResult" column. 

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi Anonymous ，

         

        So you wanna calculate the value  of “result” by column “Number ”,”x”  and “Rate”,right?

        I have modified the measure,pls see below:

        Measure = MAX('Table'[Previous Result])*MAX('Table'[Rate])+MAX('Table'[X])

        You can also create a calculated column,which is as below:

         

        Column = 'Table'[Previous Result]*'Table'[Rate]+'Table'[X]

         

        With both ways above,you will finally see as below:

         

         

        For the related .pbix file,pls click here.

         

        Hope this would help.

         

        Best Regards,

        Kelly