Forum Discussion

eacy's avatar
eacy
Helper II
9 years ago
Solved

DAX Adding offset to previous row

Hi,   I would like to create a column either in DAX or M where the some row are a date from another column and some are the previous row plus an offset, like:   IF(TABLE[INITIAL] = TRUE(); TABL...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    Hi eacy,

     

    Yes, we can achieve this requirement is Power BI by adding a calculated column. In your scenario, did you encounter any issue when using this formula?

     

    In DAX you can’t reference the previous row in any way because there is no order to the rows, So fi you need to get previous row, you can create a rank column, and then use LOOKUPVALUE function to get it.

    Rank = RANKX('TABLE','TABLE'[ID],,1)
    Previous = LOOKUPVALUE('TABLE'[CREATED],'TABLE'[Rank],'TABLE'[Rank]-1)
    Column = IF('TABLE'[INITIAL]=1,'TABLE'[CREATED],DATEADD('TABLE'[Previous],'TABLE'[OFFSET],DAY))

     

    Reference

    http://www.powerpivotpro.com/2015/03/how-to-compare-the-current-row-to-the-previous-row-in-power-pivot/

     

    Regards,

    Charlie Liao

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

Hi eacy,

 

Yes, we can achieve this requirement is Power BI by adding a calculated column. In your scenario, did you encounter any issue when using this formula?

 

In DAX you can’t reference the previous row in any way because there is no order to the rows, So fi you need to get previous row, you can create a rank column, and then use LOOKUPVALUE function to get it.

Rank = RANKX('TABLE','TABLE'[ID],,1)
Previous = LOOKUPVALUE('TABLE'[CREATED],'TABLE'[Rank],'TABLE'[Rank]-1)
Column = IF('TABLE'[INITIAL]=1,'TABLE'[CREATED],DATEADD('TABLE'[Previous],'TABLE'[OFFSET],DAY))

 

Reference

http://www.powerpivotpro.com/2015/03/how-to-compare-the-current-row-to-the-previous-row-in-power-pivot/

 

Regards,

Charlie Liao

  • eacy's avatar
    eacy
    Helper II
    9 years ago

    Hi Charlie Liao,

     

    Thanks, this sound plausible, I will try to see if I can find the time to test this today.