Forum Discussion
Calculated Column with Previous row values
Hello experts,
Can someone help me on.. how to create a calculated column in PBI desktop that populates the PREVIOUS row values.
I have the attached sample data and highlighted in yellow are the rows i wanted to get in PBI.
Thanks in advance for your help !.
Hi kumsha1 ,
If you want to use Slicer, I suggest you to create measures like so:
Rank = RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( MAX ( 'Table'[Delay Start] ) ), , ASC, DENSE )Prev Delay End Measure = VAR PreRank = [Rank] - 1 RETURN CALCULATE ( MAX ( 'Table'[Delay End] ), FILTER ( ALLSELECTED ( 'Table' ), [Rank] = PreRank ) )Prev Delay Category Measure = VAR PreRank = [Rank] - 1 RETURN CALCULATE ( MAX ( 'Table'[Delay Category] ), FILTER ( ALLSELECTED('Table'), [Rank] = PreRank ) )Prev Delay Type Measure = VAR PreRank = [Rank] - 1 RETURN CALCULATE ( MAX ( 'Table'[Delay Type] ), FILTER ( ALLSELECTED('Table'), [Rank] = PreRank ) )Prev Delay End - Delay Start Measure = VAR DateDiff_M = DATEDIFF ( MAX('Table'[Delay Start]), [Prev Delay End Measure], MINUTE ) VAR D = TRUNC ( DateDiff_M / 24 / 60 ) VAR H = TRUNC ( ( DateDiff_M - D * 24 * 60 ) / 60 ) VAR M = DateDiff_M - H * 60 - D * 24 * 60 RETURN SWITCH ( TRUE (), ISBLANK ( [Prev Delay End Measure]), BLANK (), D = 0, H & "h " & M & "m", H = 0, M & "m", D & "d " & H & "h " & M & "m" )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
If delay OID is incremental the use the first formula to get that Last OID as a new column. else use set 2 to first get the date and then get OID. Now you can create one more copy of the table and join or get into. Or just like the second formula use Previous OID calculate other into
new column Last delay OID = maxx(filter(table,table[unit]= earlier(table[unit]) && table[Delay Start]<earlier(table[Delay Start])),table[Delay OID]) //2 new column Last delay Start = maxx(filter(table,table[unit]= earlier(table[unit]) && table[Delay Start]<earlier(table[Delay Start])),table[Delay Start]) new column Last delay OID = maxx(filter(table,table[unit]= earlier(table[unit]) && table[Delay Start] = earlier(table[new column Last delay Start])),table[Delay OID])Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin.
Thanks for your reply, unfortunately none of them worked for me. I just need to get the previous row values and cant pass on filters as the data may change/users can apply filters. From the present data in the table, i just need to get the EARLIER row value. something like Previous(Column).
Hi kumsha1 ,
Try this:
1. Add an Index column in Power Query Editor.
2. Create columns.
Prev Delay End = VAR PreIndex = 'Table'[Index] - 1 RETURN CALCULATE ( MAX ( 'Table'[Delay End] ), FILTER ( 'Table', 'Table'[Index] = PreIndex ) )Prev Delay Category = VAR PreIndex = 'Table'[Index] - 1 RETURN CALCULATE ( MAX ( 'Table'[Delay Category] ), FILTER ( 'Table', 'Table'[Index] = PreIndex ) )Prev Delay Type = VAR PreIndex = 'Table'[Index] - 1 RETURN CALCULATE ( MAX ( 'Table'[Delay Type] ), FILTER ( 'Table', 'Table'[Index] = PreIndex ) )Prev Delay End - Delay Start = VAR DateDiff_M = DATEDIFF ( 'Table'[Delay Start], 'Table'[Prev Delay End], MINUTE ) VAR D = TRUNC ( DateDiff_M / 24 / 60 ) VAR H = TRUNC ( ( DateDiff_M - D * 24 * 60 ) / 60 ) VAR M = DateDiff_M - H * 60 - D * 24 * 60 RETURN SWITCH ( TRUE (), ISBLANK ( 'Table'[Prev Delay End] ), BLANK (), D = 0, H & "h " & M & "m", H = 0, M & "m", D & "d " & H & "h " & M & "m" )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply Icey. It came through just in time as i was trying the same option but the problem is...if the user try to use filters then this calculations doesn't seems to be working correct.
Hi kumsha1 ,
If you want to use Slicer, I suggest you to create measures like so:
Rank = RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( MAX ( 'Table'[Delay Start] ) ), , ASC, DENSE )Prev Delay End Measure = VAR PreRank = [Rank] - 1 RETURN CALCULATE ( MAX ( 'Table'[Delay End] ), FILTER ( ALLSELECTED ( 'Table' ), [Rank] = PreRank ) )Prev Delay Category Measure = VAR PreRank = [Rank] - 1 RETURN CALCULATE ( MAX ( 'Table'[Delay Category] ), FILTER ( ALLSELECTED('Table'), [Rank] = PreRank ) )Prev Delay Type Measure = VAR PreRank = [Rank] - 1 RETURN CALCULATE ( MAX ( 'Table'[Delay Type] ), FILTER ( ALLSELECTED('Table'), [Rank] = PreRank ) )Prev Delay End - Delay Start Measure = VAR DateDiff_M = DATEDIFF ( MAX('Table'[Delay Start]), [Prev Delay End Measure], MINUTE ) VAR D = TRUNC ( DateDiff_M / 24 / 60 ) VAR H = TRUNC ( ( DateDiff_M - D * 24 * 60 ) / 60 ) VAR M = DateDiff_M - H * 60 - D * 24 * 60 RETURN SWITCH ( TRUE (), ISBLANK ( [Prev Delay End Measure]), BLANK (), D = 0, H & "h " & M & "m", H = 0, M & "m", D & "d " & H & "h " & M & "m" )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
