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.
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)
Example:
For Work ID: 85643
I need result:
First Row: 0
Scond Row: 2.5
Third Row: 4.5-2.5 = 2
Forth Row 9.00-4.5= 4.50
Need to subtracted above row value with group by of column (Work ID and Date)
Reult look like this:
Solved! Go to Solution.
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
@Anonymous
Try this calculated column
Completed Daily = [work] - MINX ( TOPN ( 1, FILTER ( table1, [Work Item Id] = EARLIER ( [Work item Id] ) && [Changed date] > EARLIER ( [Changed date] ) ), [Changed Date], ASC ), [Work] )
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
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
Thank you so much for help
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |