Forum Discussion
Helper IITrouble Calculating the Date Difference between two fields that are the same, but different values
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 )
Helper III 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.
Super UserTry 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
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
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.