Forum Discussion
Difference between two rows with previous day, same Work Item Id
Hi Anonymous,
You can try to use below calculate column formula to achieve your requirement:
Completed Daily = VAR previous = CALCULATE ( MAX ( Test[Changed Date] ), FILTER ( ALL ( Test ), [Work Item Id] = EARLIER ( [Work Item Id] ) && [Changed Date] < EARLIER ( Test[Changed Date] ) ) ) RETURN [Work] - LOOKUPVALUE ( Test[Work], Test[Work Item Id], [Work Item Id], Test[Changed Date], previous )Regards.
Xiaoxin Sheng
Thanks This logic helpful.
But Stil have one problem. It show result in Wrong column.
it show result in wrog rows.
First Row show have result 0 and second row result is -2.5 like this
0
2
2.5
4.5
0
Hi Anonymous,
You can try to use below calculate column formula to achieve your requirement:
Completed Daily =
VAR previous =
CALCULATE (
MAX ( Test[Changed Date] ),
FILTER (
ALL ( Test ),
[Work Item Id] = EARLIER ( [Work Item Id] )
&& [Changed Date] < EARLIER ( Test[Changed Date] )
)
)
RETURN
[Work]
- LOOKUPVALUE (
Test[Work],
Test[Work Item Id], [Work Item Id],
Test[Changed Date], previous
)
Regards.
Xiaoxin Sheng
Thank you so much for help
Hi
May I know the formula in Power BI to calculate the [Gap Days] between two dates in a row for specific item?
Currently I want to calculate difference between two rows based on specific item. This is my data:
Item Date Gap Days A 1-Jan-20 A 7-Jan-20 6 A 10-Jan-20 3 B 2-Jan-20 B 5-Jan-20 3 C 8-Jan-20 C 16-Jan-20 8 C 25-Jan-20 9 D 15-Jan-20 D 31-Jan-20 16 The gap days is the dates in the row minus the date in the previous row for the same item.
Really appreciate your help.
Thanks
Regards,
Caroline