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 actually did reply to this answer, but for some reason it didn't post here.  This solution allowed my measure to save without error.  However, I don't get any results with it.  I checked my dataset, using three of the files from the data output screenshot above and there is data for both received/completed tasks that I'm trying to measure.
Unsure what to do next.

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

    Try creating a calculated table like

    Tmp Table =
ADDCOLUMNS (
    VALUES ( 'SPSOrder'[Order ID] ),
    "TitleWork",
        CALCULATE (
            MAX ( 'SPSTask'[Completed/Received Date] ),
            'SPSTask'[TaskLookupCode] = "TitleWork"
        ),
    "SignFinalCommitment",
        CALCULATE (
            MAX ( 'SPSTask'[Completed/Received Date] ),
            'SPSTask'[TaskLookupCode] = "SgnFnlCom"
        )
)

    You could then have a look in the data view and see if that gives any insights

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