Forum Discussion
Date Diff between two separate tables
I'm trying to calculate the time between two dates, but the two dates are in separate tables that I've imported from an SQL Server. The tables are linked by a column called WORKORDERID as a one to many relationship. The Request Table has only blanks for the WORKORDERID and the WorkOrder Table has distinct values for the WORKORDERID column.
I want to find the time it takes from the Request table Initiate Date (in Date AND Time format) to the WorkOrder table initiate date (in Date AND Time format).
Hi jtech1343 ,
I created a sample pbix file(see the attachment), please check if that is what you want. You can create a calculated column as below to get it:
Hour Diff = DATEDIFF ( CALCULATE ( MAX ( WorkOrder[Initiate Date] ), FILTER ( 'WorkOrder', 'WorkOrder'[WORKORDERID] = 'Request'[WORKORDERID] ) ), 'Request'[Initiate Date], HOUR )With duration format:
Diff Duration = VAR vSeconds = DATEDIFF ( CALCULATE ( MAX ( WorkOrder[Initiate Date] ), FILTER ( 'WorkOrder', 'WorkOrder'[WORKORDERID] = 'Request'[WORKORDERID] ) ), 'Request'[Initiate Date], SECOND ) VAR vMinutes = INT ( vSeconds / 60 ) VAR vRemainingSeconds = MOD ( vSeconds, 60 ) VAR vHours = INT ( vMinutes / 60 ) VAR vRemainingMinutes = MOD ( vMinutes, 60 ) VAR vDays = INT ( vHours / 24 ) VAR vRemainingHours = MOD ( vHours, 24 ) RETURN IF ( 'Request'[WORKORDERID] <> BLANK (), vDays & " Days & " & vRemainingHours & " Hours & " & vRemainingMinutes & " Minutes & " & vRemainingSeconds & " Seconds" )Reference links:
Solved: Date Difference between 2 different tables - Microsoft Fabric Community
Solved: Calculate Duration from date time fields - Microsoft Fabric Community
Calculate Duration in Days Hours Minutes and Seconds Dynamically in Power BI using DAX - RADACAD
Best Regards
2 Replies
Hi jtech1343 ,
I created a sample pbix file(see the attachment), please check if that is what you want. You can create a calculated column as below to get it:
Hour Diff = DATEDIFF ( CALCULATE ( MAX ( WorkOrder[Initiate Date] ), FILTER ( 'WorkOrder', 'WorkOrder'[WORKORDERID] = 'Request'[WORKORDERID] ) ), 'Request'[Initiate Date], HOUR )With duration format:
Diff Duration = VAR vSeconds = DATEDIFF ( CALCULATE ( MAX ( WorkOrder[Initiate Date] ), FILTER ( 'WorkOrder', 'WorkOrder'[WORKORDERID] = 'Request'[WORKORDERID] ) ), 'Request'[Initiate Date], SECOND ) VAR vMinutes = INT ( vSeconds / 60 ) VAR vRemainingSeconds = MOD ( vSeconds, 60 ) VAR vHours = INT ( vMinutes / 60 ) VAR vRemainingMinutes = MOD ( vMinutes, 60 ) VAR vDays = INT ( vHours / 24 ) VAR vRemainingHours = MOD ( vHours, 24 ) RETURN IF ( 'Request'[WORKORDERID] <> BLANK (), vDays & " Days & " & vRemainingHours & " Hours & " & vRemainingMinutes & " Minutes & " & vRemainingSeconds & " Seconds" )Reference links:
Solved: Date Difference between 2 different tables - Microsoft Fabric Community
Solved: Calculate Duration from date time fields - Microsoft Fabric Community
Calculate Duration in Days Hours Minutes and Seconds Dynamically in Power BI using DAX - RADACAD
Best Regards
