Forum Discussion

axk180022's avatar
axk180022
Helper II
4 years ago
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.   ...
Data Protection
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

axk180022's avatar
axk180022
Helper II
4 years ago

The one highlighted is the output I require, Which is the difference of GenHours between 2 latest dates. 

 

richbenmintz 

richbenmintz's avatar
richbenmintz
Resident Rockstar
4 years ago

Hi axk180022 ,

 

could you provide the data?

  • axk180022's avatar
    axk180022
    Helper II
    4 years ago

    Since Im a new user im unable to upload the above data. is there a link you can provide me?

    richbenmintz 

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      4 years ago

      Hi axk180022 ,

       

      You can paste the data in a table, or share your pbix through google drive or onedrive

      • axk180022's avatar
        axk180022
        Helper II
        4 years ago
        serialidGenHoursDates
        cvs-001 30012/1/2019
        cvs-001 2001/2/2020
        cvs-001 2002/2/2020
        cvs-002 8004/3/2020
        cvs-002 3005/3/2020
        cvs-002 4006/3/2020
        cvs-003 2505/5/2020
        cvs-004 8205/2/2020
        cvs-004 3506/6/2020
        cvs-004 1506/7/2020

         

        Here is the table richbenmintz