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
)
kkirner's avatar
kkirner
Icon for Helper II rankHelper II
3 years ago

I like this!  I hadn't thought of it before.  Oddly enough, it didn't give me results in the SignFinalCommitment column until I added "|| 'SPSTask'[Task Name] = "Review and Sign Final Commitment".  I could live with that if I had to.
But, when I add that to the measure, I still don't get any results.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

Are there any other columns in the visual which could be affecting things? The results in the calculated table and the measure should be identical.