Trying to calculate the elapsed time between the completion of two tasks in the same file

[kkirner]

I have a scenario where I have files, all with unique order numbers.  Each file contains multiple tasks.  The completion date/time of a task is recorded in the file using the table[field] SPSTask[Completed/Received Date].  There's another field in the table called SPSTask[TaskLookupCode].  I'm having a hard time measuring the time difference between the completion of one task and the completion of another task.
Initially I created a measure to show the completion of each task separately using: 

Completed Date-BscOrdEnt =

var TaskCompDate = VALUES(SPSTask[Completed/Received Date])

var EndBscOrdEnt = CALCULATE(TaskCompDate, SPSTask[TaskLookupCode]="BscOrdEnt")

return EndBscOrdEnt

and

Completed Date-AdvOrdEnt =

var TaskCompDate = VALUES(SPSTask[Completed/Received Date])

var EndAdvOrdEnt = CALCULATE(TaskCompDate, SPSTask[TaskLookupCode]="AdvOrdEnt")

return EndAdvOrdEnt

Then a simple DATEDIFF measure:
BscOrd to AdvOrd = 

DATEDIFF([Completed Date-BscOrdEnt], [Completed Date-AdvOrdEnt],MINUTE)

Here is my output:

I get two rows for the same file number the completed time of the first task shows in each column on the first row, the completed time for the second task shows on the second row.  The last column is my DATEDIFF attempt and I only get zeros showing.  I want one row with the difference in time showing. 

Thank you!

[kkirner]

@EylesIT there are 807,783 rows and the source is SQL.

[EylesIT]

@kkirner, here is my suggested solution.

I am using the data for orderNo 12345 in a table called SPSTask as per below:

OrderNo	TaskLookupCode	CompletedDate
12345	BscOrdEnt	10 Feb 2023 13:12:35
12345	AdvOrdEnt	10 Feb 2023 13:42:02

I then created two measures to calculate the Completed Date for the BscOrdEnt and AdvOrdEnt task codes for the selected OrderNo:

Completed Date-BscOrdEnt = 
    VAR orderno = SELECTEDVALUE(SPSTask[OrderNo])
    RETURN
        CALCULATE(
            MAX(SPSTask[CompletedDate]),
            SPSTask[OrderNo] = orderno,
            SPSTask[TaskLookupCode] = "BscOrdEnt"
        )

Completed Date-AdvOrdEnt = 
    VAR orderno = SELECTEDVALUE(SPSTask[OrderNo])
    RETURN
        CALCULATE(
            MAX(SPSTask[CompletedDate]),
            SPSTask[OrderNo] = orderno,
            SPSTask[TaskLookupCode] = "AdvOrdEnt"
        )

I then create a measure to calculate the difference in seconds between those two:

BscOrd to AdvOrd = 
    DATEDIFF([Completed Date-BscOrdEnt], [Completed Date-AdvOrdEnt], SECOND)

I then create a measure to display the [BscOrd to AdvOrd] measure in "d h m s" format:

BscOrd to AdvOrd DHMS = 
    VAR DAXDay   = INT([BscOrd to AdvOrd]/(24*60*60))
    VAR DAXHours = MOD(INT([BscOrd to AdvOrd]/(60*60)),24)
    VAR DAXMin   = MOD(INT([BscOrd to AdvOrd]/60),60)
    VAR DAXSec   = MOD(    [BscOrd to AdvOrd],60)
    RETURN DAXDay & "days " & DAXHours & "h " & DAXMin & "m " & DAXSec & "s"

Then I add a table visual to the report, and add in the OrderNo column, changing its column option to "Don't summarize".

Then I add the four measures created above. This gives me the following output:

Is this the sort of thing you are looking for?

[kkirner]

@EylesIT Thank you for this and yes, that is exactly the output I'm looking for.  Unfortunately, it isn't working for me.  Below is a screenshot for my table visual.  The only filter I have on the visual is one limiting the number of orders based on the date we received them.  As you can see I'm getting the same Order Number for each property and no results for any of the measures based on what you provided.

[EylesIT]

@kkirner, you say you are getting the same Order Number for each property. I need to understand you data structure a bit more.

Can an Order Number have just one Property Address, or can an Order Number have more than one Property Address?

Do the fields for Order Number and Property Address in your table visual come from that same source table?

Does the Date Order Received field that you are filtering on also come from the same table?

I would suggest removing the [BscOrd to AdvOrd DHMS] measure from the table visual and then see how it looks.

[kkirner]

@EylesIT I am good now.  I don't know exactly what the "same order number different addresses issue" was, but I removed the DHMS measure as you said and then I looked it over and found I had to wrap it in an "IF [BscOrd to AdvOrd]>0, Then run through the measure.  It now works fine as shown below and I'll accept this as solution.  Thank you very very much!

[v-binbinyu-msft]

Hi @kkirner ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu

[kkirner]

Hi @v-binbinyu-msft, How can I get you the desensitized data?  I've tried uploading the .pbix and the exported data in .csv format.  This site won't allow either.

Thank you!

[kkirner]

Hi @v-binbinyu-msft , Thank you for your response.  I think I'm including everything you asked for to better understand my request for help.  I have a spreadsheet below, where the top portion os what I currently have in place, with some explanations.  Starting in row 13 is the same information, but showing my desired result.  Hopefully, you'll be able to enlarge it enough for it to make sense.

[kkirner]

I also tried this, which was based off of a solution posted on a similar issue.  It's been trying to save for 10-15 minutes, maybe longer.  Does anyone have any ideas?

[EylesIT]

@kkirner, how many rows are in your SPSTask table? And what is the table source (Excel file, SQL Database, ...?)