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
eacy
Helper II
Helper II

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();
  TABLE[CREATED];
  DATEADD(TABLE[value from previous row];TABLE[OFFSET])
)

any suggestions are appriciated

 

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

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

Capture.PNG

 

Reference

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

 

Regards,

Charlie Liao

View solution in original post

2 REPLIES 2
v-caliao-msft
Employee
Employee

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

Capture.PNG

 

Reference

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

 

Regards,

Charlie Liao

Hi Charlie Liao,

 

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

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.