Forum Discussion

dnsia's avatar
dnsia
Icon for Helper II rankHelper II
5 years ago
Solved

How to calculate date diff between two rows and columns

Hi all, 

Please help. 


 

BL NoContainer NoLoad Discharge DCHF TRFFRCVFSSTRSNTC RCVE
BL0001CONT0001PORT APORT D6/1/2020 4:12 6/1/2020 10:50    6/8/2020 15:12
BL0001CONT0001PORT APORT D     6/5/2020 10:03  
BL0002CONT0002PORT APORT D6/1/2020 4:12 6/1/2020 10:50    6/5/2020 11:56
BL0002CONT0002PORT APORT D     6/4/2020 15:47  
BL0003CONT0003PORT APORT D6/1/2020 4:12 6/1/2020 9:40    6/10/2020 10:46
BL0003CONT0003PORT APORT D     6/5/2020 13:50  


I need to calculate date difference for the following

1. DEM Column (in Days) = SNTC and DCHF  columns of the same BL Number and Container Number (ie. BL0001 DEM is 4 -- date diff bet 6/5/2020 10:03 and 6/1/2020 4:12).

2. DET Column (in Days) = RCVE and SNTCcolumns of the same BL Number and Container Number (ie. BL0001 DET is 3 -- date diff bet 6/8/2020 15:12 and 6/5/2020 10:03).


BL NoContainer NoLoadDischargeDCHF TRFFRCVFSSTRSNTC RCVEDemDet
BL0001CONT0001PORT APORT D6/1/2020 4:12 6/1/2020 10:50    6/8/2020 15:12 3
BL0001CONT0001PORT APORT D     6/5/2020 10:03  4 
BL0002CONT0002PORT APORT D6/1/2020 4:12 6/1/2020 10:50    6/5/2020 11:56 1
BL0002CONT0002PORT APORT D     6/4/2020 15:47  3 
BL0003CONT0003PORT APORT D6/1/2020 4:12 6/1/2020 9:40    6/10/2020 10:46 5
BL0003CONT0003PORT APORT D     6/5/2020 13:50  4 
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests
  • Fowmy's avatar
    Fowmy
    5 years ago

    dnsia 

    Here are the two columns that you need to add:

    Dim = 
var __bilno = [BL No] 
var __sntc = 
    MAXX( FILTER( table11 , Table11[BL No] = __bilno ) , Table11[SNTC])
var __dchf = 
    MAXX( FILTER( table11 , Table11[BL No] = __bilno ) , Table11[DCHF])
return
   IF( Table11[SNTC] <> BLANK() , DATEDIFF( __dchf, __sntc , DAY ) )
    Det = 
var __bilno = [BL No] 
var __rcve = 
    MAXX( FILTER( table11 , Table11[BL No] = __bilno ) , Table11[SNTC])
var __dchf = 
    MAXX( FILTER( table11 , Table11[BL No] = __bilno ) , Table11[RCVE])
return
   IF( Table11[RCVE] <> BLANK() , DATEDIFF( __rcve, __dchf , DAY ) )

     

     



2 Replies

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

    dnsia 

    Here are the two columns that you need to add:

    Dim = 
var __bilno = [BL No] 
var __sntc = 
    MAXX( FILTER( table11 , Table11[BL No] = __bilno ) , Table11[SNTC])
var __dchf = 
    MAXX( FILTER( table11 , Table11[BL No] = __bilno ) , Table11[DCHF])
return
   IF( Table11[SNTC] <> BLANK() , DATEDIFF( __dchf, __sntc , DAY ) )
    Det = 
var __bilno = [BL No] 
var __rcve = 
    MAXX( FILTER( table11 , Table11[BL No] = __bilno ) , Table11[SNTC])
var __dchf = 
    MAXX( FILTER( table11 , Table11[BL No] = __bilno ) , Table11[RCVE])
return
   IF( Table11[RCVE] <> BLANK() , DATEDIFF( __rcve, __dchf , DAY ) )

     

     



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

      This is working fine! Thank you so much for your help!