Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Regards,
Charlie Liao
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |