Forum Discussion

kkirner's avatar
kkirner
Icon for Helper II rankHelper II
3 years ago
Solved

Trouble Calculating the Date Difference between two fields that are the same, but different values

Good morning!  I'm relatively new to Power BI.  I have a decent understanding of DAX, but have always struggled with the Date/Time calculations. I have a report with multiple tables, but I think wha...
  • johnt75's avatar
    johnt75
    3 years ago

    Try

    Days Diff Title Work to Sign Final Commitment =
SUMX (
    VALUES ( 'SPSOrder'[Order ID] ),
    VAR TitleWork =
        CALCULATE (
            MAX ( 'SPSTask'[Completed/Received Date] ),
            'SPSTask'[TaskLookupCode] = "TitleWork"
        )
    VAR SignFinalCommitment =
        CALCULATE (
            MAX ( 'SPSTask'[Completed/Received Date] ),
            'SPSTask'[TaskLookupCode] = "SgnFnlCom"
        )
    VAR Result =
        DATEDIFF ( TitleWork, SignFinalCommitment, DAY )
    RETURN
        Result
)
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

Try

Days Diff Title Work to Sign Final Commitment =
SUMX (
    VALUES ( 'SPSOrder'[Order ID] ),
    VAR TitleWork =
        CALCULATE (
            MAX ( 'SPSTask'[Completed/Received Date] ),
            'SPSTask'[TaskLookupCode] = "TitleWork"
        )
    VAR SignFinalCommitment =
        CALCULATE (
            MAX ( 'SPSTask'[Completed/Received Date] ),
            'SPSTask'[TaskLookupCode] = "SgnFnlCom"
        )
    VAR Result =
        DATEDIFF ( TitleWork, SignFinalCommitment, DAY )
    RETURN
        Result
)
kkirner's avatar
kkirner
Icon for Helper II rankHelper II
3 years ago

johnt75 - thank you.  This is the one that didn't error out, but also didn't give any results.