Date Diff between two separate tables

[jtech1343]

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

[Anonymous · Accepted solution]

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

[pallavi_r]

Hi @jtech1343 ,

Please refer this link if it solves your problem.

https://community.fabric.microsoft.com/t5/Power-Query/Date-difference-between-two-different-tables-c...

Thanks,

Pallavi