Forum Discussion
Difference between two rows with previous day, same Work Item Id
Completed Daily = for a given row, it is the difference between the Completed Work column and the previous value (previous day, same Work Item Id) http://prntscr.com/ktxpn4 Exampl...
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
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
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