topic Re: Trouble Calculating the Date Difference between two fields that are the same, but different values in DAX Commands and Tips

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

kkirner — Thu, 11 May 2023 14:24:26 GMT

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 what's important to this question is the Task and Order tables. Here's how they relate to each other.

The order table contains data for each file we work on, like order number, property address, buyer/seller names, etc. The task table contains a variety of tasks that need to be done to complete the file. Each task has a unique name and lookup code, multiple statuses, a created date, a requested date and a received/completed date. I'm trying to calculate the DATEDIFF between the

SPSTask[Completed/Received Date] for two different tasks. For instance when the TitleWork task was received to the Sign Final Commitment received date.
This is an example of how the data output would look (this screenshot is measuring the Requested Date to the Received Date for one (or the same) task.

Here's my DAX Measure - I receive an error "Multiple columns cannot be converted to a scalar value". I've tried reworking it several ways and haven't had any success. My thought process was to define the two tasks involved, then if it was those tasks involved use the completed/received date for them and calculate the datediff.

I'd appreciate any help provided.

TitleRec'd to SgnFnlComm TAT =
var TWTaskDefined = SUMX(VALUES(SPSTask[Task Name]),FILTER(SPSTask,SPSTask[TaskLookupCode]="TitleWork"))
var CommitTaskDefined = SUMX(VALUES(SPSTask[Task Name]),FILTER(SPSTask,SPSTask[TaskLookupCode]="SgnFnlCom"))
var TWTaskRecdDate = If(TWTaskDefined="True", Values(SPSTask[Completed/Received Date]))
var CommitTaskRecdDate = IF(CommitTaskDefined="True",VALUES(SPSTask[Completed/Received Date]))
var DayCalcTWCommit = DATEDIFF(TWTaskRecdDate,CommitTaskRecdDate,DAY)
return DayCalcTWCommit

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

johnt75 — Thu, 11 May 2023 15:54:41 GMT

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 )

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

kkirner — Fri, 12 May 2023 11:53:35 GMT

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.

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

johnt75 — Fri, 12 May 2023 12:37:51 GMT

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

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

kkirner — Fri, 12 May 2023 14:30:13 GMT

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.

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

kkirner — Fri, 12 May 2023 14:38:58 GMT

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

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

johnt75 — Fri, 12 May 2023 14:44:44 GMT

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.

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

kkirner — Fri, 12 May 2023 15:01:34 GMT

johnt75 Thank you! You nailed it. I removed all of the columns other than the Order ID and got the DateDiff results. I appreciate all of your help!

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

johnt75 — Fri, 12 May 2023 15:06:15 GMT

If you need to put them back in the visual you could add REMOVEFILTERS clauses for those columns into the CALCULATE calls.