Forum Discussion

axk180022's avatar
axk180022
Icon for Helper II rankHelper II
5 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
    5 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.

richbenmintz's avatar
richbenmintz
Icon for Resident Rockstar rankResident Rockstar
5 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
Icon for Helper II rankHelper II
5 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 

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    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

     

    • richbenmintz's avatar
      richbenmintz
      Icon for Resident Rockstar rankResident Rockstar
      5 years ago

      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())

       

       

    • axk180022's avatar
      axk180022
      Icon for Helper II rankHelper II
      5 years ago

      Thank you so much, this solution worked, but is it possible to make the other values to be left blank?   like below

      serialidGenHoursDatesDifference
      cvs-00130012/1/2019 
      cvs-0012001/2/2020 
      cvs-0012002/2/20200
      cvs-0028004/3/2020 
      cvs-0023005/3/2020 
      cvs-0024006/3/2020100
      cvs-0032505/5/2020250
      cvs-0048205/2/2020 
      cvs-0043506/6/2020 
      cvs-0041506/7/2020200

       

      richbenmintz 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 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.