How to Sum the Lowest Time and Highest Time in series of Tasks

[Shakeerm]

Hey Guys..

I am struggling a wee bit with this requirement i have where i have a list of Tasks where it represents the Lowest Time and the Highest Time for each Task. 

These Tasks are for One Job i.e. User Tasks for an Export Sea Job in our system. And there are about 800 Jobs for a Month. Therefore we're looking for about 6400 Tasks for a Month. 

Both the Low & the High Columns are in Time value. (In Minutes) 

what i want to do is Calculate the Time difference for each Task and Sum the total to see the total time for a each Job. 

Greatly appreciate if anyone can help to solve this

[Greg_Deckler]

So in theory create column diff, [High] - [Low] and then do a SUM across it?

See this link which has how to aggregation duration. https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[Shakeerm]

Hey @Greg_Deckler 

So this will retrun the value in Seconds Correct? 

[Shakeerm]

Hi @Greg_Deckler 

I'm getting the same Value for all the different Minutes 

Blue Highlighted is the difference in Minutes and the Red is the Formula which i included. 

[Greg_Deckler]

You are using the measure form in a calculated column. Ditch the SUM in the first line.

[Shakeerm]

Hi @Greg_Deckler 

Still doesn't work mate.

Tried using a new measure and If you see below the Highlighted Job, there are 07 Tasks in this Job and the duration of all the Tasks should be 48 mins. but here it shows only 8. 

[Greg_Deckler]

So is that the only one that is wrong or are the others wrong? It's really, really, really difficult to troubleshoot these things from a picture of data and no information on the data model or anything else. I have no idea what items you have in your visuals. I don't even know what I am looking at in that second image. So, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But, in general, what I would recommend is to convert your duration items to seconds. Subtract them. Make sure that this is working correctly first at the individual task level. If it is, go to step 2, you should be able to sum these differences and then convert it back into minutes.

[Shakeerm]

Hey @Greg 

Sorry Mate. Here's the .pbix file and an excel File containing what i'm looking for. The red highlight total is what i'm looking for in all the Jobs in my report and to present it via a Visual. 

https://drive.google.com/drive/folders/1GpI1rjBfOBiLF0Q-qYZstmStFR10x9f-?usp=sharing 

please do let me know. Appreciate the help. 

[v-shex-msft · Accepted solution]

HI @Shakeerm ,

You can try to use the following measure formula to display the summary durations:

AllocatedDuration Modified = 
VAR summary =
    SUMMARIZE (
        'HR Performance Analysis Report',
        [Job No],
        [Branch Code],
        [Department Code],
        [Allocated Time],
        "TotalSecond", HOUR ( [Allocated Time] ) * 3600
            + MINUTE ( [Allocated Time] ) * 60
            + SECOND ( [Allocated Time] )
    )
VAR totalseconds =
    SUMX ( summary, [TotalSecond] )
VAR totalhours =
    INT ( totalseconds / 3600 )
VAR Days =
    INT ( totalhours / 24 ) + 0
VAR Hours = totalhours - Days * 24
VAR Minutes =
    INT ( ( totalseconds - totalhours * 3600 ) / 60 )
VAR Seconds = totalseconds - totalhours * 3600 - Minutes * 60
VAR duration = Hours & ":" & Minutes & ":" & Seconds
RETURN
    IF ( Days > 0, Days & "D " & duration, duration )

Notice: power bi does not support duration type values and time value does not allow to greater than 24 hours, so I split the aggregate duration value to two parts with duration day(marked with 'D') and times.

Regards,

Xiaoxin SHeng

[Shakeerm]

Hi Xiaoxin

Thanks alot again for this complex formula. it helps alot. However i have notice an issue when validating the date Durations. 

I've applied the same measure to a the formula provided and it gave me only the difference in Hours. The count of dates seemed to be missing. 

for instance,

Start Date : 5/29/2020 13:05

End Date : 6/4/2020 14:35

 the result should be 06 Days, 01 Hour, 30 Mins. But the Visual only shows 01 Hour 30 Mins. 

Could you help to sort this for me please. 

[v-shex-msft]

Hi @Shakeerm,

Please share some dummy data with minimum data structure to test.

Regards,

Xiaoxin Sheng

[Shakeerm]

@v-shex-msft 

here's the link for the data. Appreciate the help mate

https://drive.google.com/file/d/1UTOoKZlFPMW4fxKpf3Tl-2_xl2zIxvdQ/view?usp=sharing 

[Shakeerm]

Tagging @v-shex-msft

Need your help on this Mate... 

[Shakeerm]

@v-shex-msft 

Thanks a Ton Mate. This makes quite sense now. 

Cheers