Having Trouble Figuring the Average Turnaround Time

[kkirner]

Hi, several weeks ago @EylesIT helped me big time by solving for me the calculation of the time difference between the completion of one task in our files to the completion of a second task in our file.

Now, I'd like to solve for the average time for each task category.

I'll try and lay it out to help you understand it better.

1. We're an insurance agency with one file for each property.
2. Each file has multiple tasks that have to be completed.
3. Each task has a "completed/received date" in the database.
4. Therefore, each file will have multiple completed/received dates.

What @EylesIT helped with was figuring the time difference out, by using a series of measures, shown here:

Completed-AdvOrdEnt =

VAR orderno = SELECTEDVALUE(SPSTask[Order Number])

    RETURN

        CALCULATE( MAX(SPSTask[Completed/Received Date]),

            SPSTask[Order Number] = orderno,

            SPSTask[TaskLookupCode] = "AdvOrdEnt" )

-----------------------

BscOrd to AdvOrd =

    DATEDIFF([Completed-BscOrdEnt], [Completed-AdvOrdEnt], SECOND)

-----------------------

BscOrd to AdvOrd DHMS = IF([BscOrd to AdvOrd]>0,

    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")

-------------------------

My output looks like this, which is fabulous:

What I'm trying to do is take the sum of the second measure, where it's calculating the datediff in seconds, and then divide it by the number of specific task completions during the time period I'm looking at.  I've tried several different things and can't get anything to work.

[kkirner]

Thanks.  I just deleted those two.  We were using them at all.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[kkirner]

@lbendlin thank you for the reply and the helpful information.  Here's a link to my .pbix in OneDrive: 

Turnaround Time-Title Tasks 

Each file has multiple tasks and each task has it's own completion date/time.

The first page has two tables, measuring different timeframes.  Both, show that data in DHMS format in the last or second to last column on the right.  The measurement at the top (from the time we opened/created the file to the time our first task was completed) has additional visuals next to the table, showing the total time, for each file that meets the criteria in the table, in seconds...summed.  There's another visual there with the count of the order numbers, for each file that meets the criteria in the table.  If I just did the math, I'd divide the 249,363 by 233 and come up with 1,070 seconds as the average.  It just doesn't seem to be as easy as that.

The second measurement is from the completion of the first task to the completion of the second task.  I'd like to do the same thing...get the total time it took to complete all of the tasks for the files shown and divide by the number of files involved in this measurement to get the average.

The second page shows some averages I have calculated, but they are from the beginning of the file til the end of a certain task, and other than that first calculation, that's not what I'm trying to get.

[lbendlin]

You may want to get your data model in order first. 

what's the meaning of thr Event and SettlementCalendar tables? Can they be folder into the Calendar table?

[kkirner]

I deleted those two.  We weren't using them at all.