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.
Hi axk180022 ,
You can paste the data in a table, or share your pbix through google drive or onedrive
| serialid | GenHours | Dates |
| cvs-001 | 300 | 12/1/2019 |
| cvs-001 | 200 | 1/2/2020 |
| cvs-001 | 200 | 2/2/2020 |
| cvs-002 | 800 | 4/3/2020 |
| cvs-002 | 300 | 5/3/2020 |
| cvs-002 | 400 | 6/3/2020 |
| cvs-003 | 250 | 5/5/2020 |
| cvs-004 | 820 | 5/2/2020 |
| cvs-004 | 350 | 6/6/2020 |
| cvs-004 | 150 | 6/7/2020 |
Here is the table richbenmintz
Hi axk180022
If you create a calc column that get the prior date value like
Prior Date Value = var _serialid = [serialid] var _date = [Dates] return CALCULATE(MAX('Table'[GenHours]), TOPN(1, FILTER('Table', [serialid] = _serialid && [Dates]<_date), 'Table'[Dates], DESC))you can create another column that derives the difference
diff = [GenHours] - [Prior Date Value]hope this helps
Hi axk180022,
See Formula Below, combined logic into single column and set value to blank when not the max date per serial number
Difference = var _serialid = [serialid] var _date = [Dates] return if('Table'[Dates] = CALCULATE(MAX('Table'[Dates]), filter(ALL('Table'),'Table'[serialid] = _serialid)), abs('Table'[GenHours] - CALCULATE(MAX('Table'[GenHours]), TOPN(1, FILTER('Table', [serialid] = _serialid && [Dates]<_date), 'Table'[Dates], DESC)) ) , BLANK())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.