Forum Discussion

harshagraj's avatar
harshagraj
Icon for Post Partisan rankPost Partisan
5 years ago

How to get row to row difference by Group/sort

Hello all, I need to group/sort by ModulNr and StationsNr and need the datediff in secs by row2 - row1,row3-row2. Attached the data and also screenshot. Please note I am in a direct query mode.  Tha...
Need Help
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
5 years ago

harshagraj 

maybe you can try to create a measure

Measure = 
VAR arrive=max('Table'[PreStopperArrival_C])
VAR Previous=MAXX(FILTER(all('Table'),'Table'[ModulNr]=max('Table'[ModulNr])&&'Table'[StationsNr]=max('Table'[StationsNr])&&'Table'[PreStopperArrival_C]<arrive),'Table'[PreStopperArrival_C])
return if(ISBLANK(Previous),BLANK(),SECOND(arrive-Previous))

harshagraj's avatar
harshagraj
Icon for Post Partisan rankPost Partisan
5 years ago

Hello ryan_mayu  thanks a lot. Its working perfectly. But I need to plot Barchart. X axis will have Concatenated Module Station Nr and Y axis i have to consider Avergae Carriertime.

Carriertime(Measure) = Above calculated field  minus(-) Prestoparrdiff

Prestoparrdiff(Measure)=DATEDIFF(SELECTEDVALUE(CTM_Timestamps[PreStopperArrival_C]),SELECTEDVALUE(CTM_Timestamps[DepositCarrier_C]),SECOND)
will the above calculation works with Barchart? I tried but ended up in wrong values
 
  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    harshagraj 

    I keep working on the previous sample data. you can try to create a measure

    average = averagex(CTM_Timestamps,[Carriertime])

    please see the attachment below

     

    • harshagraj's avatar
      harshagraj
      Icon for Post Partisan rankPost Partisan
      5 years ago

      Hello ryan_mayu thanks a lot for the help. I just exactly did the same steps. But I am getting below error. My data size is huge actually. Every 5 seconds i get 8 records for different modules and from diff stations

       

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        5 years ago

        harshagraj 

        I seldom us direct query. can pbi display the bottom left table on your end? That table contains the same fields and measures as the chart.