Forum Discussion
Solved
Calculate Difference between 2 rows based on another column filter
I would want to find the difference in the GenHours for each serialid. Currently this is how my table looks on power BI. I have filtered the latest and 2nd latest date for each serialid. ...
Hi,
This calculated column formula works
=if(CALCULATE(MAX(Data[Dates]),FILTER(Data,Data[serialid]=EARLIER(Data[serialid])))=Data[Dates],ABS(Data[GenHours]-LOOKUPVALUE(Data[GenHours],Data[Dates],CALCULATE(max(Data[Dates]),FILTER(Data,Data[serialid]=EARLIER(Data[serialid])&&Data[Dates]<EARLIER(Data[Dates]))),Data[serialid],Data[serialid])),BLANK())Hope this helps.
Thank you so much, this solution worked, but is it possible to make the other values to be left blank? like below
| serialid | GenHours | Dates | Difference |
| cvs-001 | 300 | 12/1/2019 | |
| cvs-001 | 200 | 1/2/2020 | |
| cvs-001 | 200 | 2/2/2020 | 0 |
| cvs-002 | 800 | 4/3/2020 | |
| cvs-002 | 300 | 5/3/2020 | |
| cvs-002 | 400 | 6/3/2020 | 100 |
| cvs-003 | 250 | 5/5/2020 | 250 |
| cvs-004 | 820 | 5/2/2020 | |
| cvs-004 | 350 | 6/6/2020 | |
| cvs-004 | 150 | 6/7/2020 | 200 |
Hi,
This calculated column formula works
=if(CALCULATE(MAX(Data[Dates]),FILTER(Data,Data[serialid]=EARLIER(Data[serialid])))=Data[Dates],ABS(Data[GenHours]-LOOKUPVALUE(Data[GenHours],Data[Dates],CALCULATE(max(Data[Dates]),FILTER(Data,Data[serialid]=EARLIER(Data[serialid])&&Data[Dates]<EARLIER(Data[Dates]))),Data[serialid],Data[serialid])),BLANK())Hope this helps.