Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kkirner
Helper II
Helper II

Having Trouble Figuring the Average Turnaround Time

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:
kkirner_1-1700150741481.png

 

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.


 

 
5 REPLIES 5
kkirner
Helper II
Helper II

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

lbendlin
Super User
Super User

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

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

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

lbendlin_0-1700511437955.png

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.