Get date and time difference in duration as time - or why I need dd.hh.mm.ss

[SysLostInBI]

I need to comply a report that calculates SLAs.

I got my data from statuscake in "periods" which means I got periods where the service is up, and periods where the service is down.

Example:

Start	End	Status
31.05.2021 17:25	01.06.2021 00:00	Up
31.05.2021 17:24	31.05.2021 17:24	Down
30.05.2021 12:25	30.05.2021 12:25	Up
30.05.2021 12:24	30.05.2021 12:24	Down
25.05.2021 21:49	25.05.2021 21:49	Up

Now, I manage to extract the difference between the dates with:

DATEDIFF([Start],[End],SECOND),

This gives me seconds. Which I could use to create a report "Sum of Seconds per Status".
But it would be nice to have a "Days, Hours, Minutes, Seconds per Status" instead.

I did some research around this topic already, and I found a lot of ways to calucate Time from seconds into hh:mm:ss, which seems to be a supported format - until - you have more than 24h timeframes. 
So I found ways to format d:hh:mm:ss or d.hh:mm:ss - but this format is not known by PowerBi, as it seems - and therefore I can't get a "SUM by Status".

Any help is appreciated. 

[v-xiaotang]

Hi @SysLostInBI 

Based on your discription, you want to calculate the "Days, Hours, Minutes, Seconds per Status" instead of "sum of seconds per Status", but in Power BI there is no such format. So I suggest you create the column and measure bellow to custom your format "day:hh:mm:ss", e.g.

secondsColumn = DATEDIFF('Table'[Start],'Table'[End],SECOND)

d:hh:mm:ss perstatus = 
var _sumseconds_perstatus=CALCULATE(SUM('Table'[secondsCol]),ALLEXCEPT('Table','Table'[Status]))//calculate sum seconds per status
var _day=INT(_sumseconds_perstatus/(24*3600))
var _hour=INT((_sumseconds_perstatus-_day*(24*3600))/3600)
var _min=INT((_sumseconds_perstatus-_day*(24*3600)-_hour*3600)/60)
var _sec=INT((_sumseconds_perstatus-_day*(24*3600)-_hour*3600-_min*60))
return
_day&":"&_hour&":"&_min&":"&_sec

See sample file attached bellow.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

[SysLostInBI]

Hey thanks a lot @v-xiaotang 
I managed to do this already. Sorry if I wasn't clear about this.
My problem is that I'm stuck with this afterwards. What I would need is an option to calculate these values afterwards.

So, let's say I have:

My Result should be:
Up: 10 days
Down: 3 days and 1h

I guess I would need to create another table for this - as the "duration" support of PowerBI is weak for this right? I mean the best option would be if I could just "group" it, and it is calculated.

[v-xiaotang · Accepted solution]

Hi @SysLostInBI 
even though you could "group", it won't do calculations because the type of duration(format: dd:hh:mm:ss) is string. by the way, there is no format of duration(dd:hh:mm:ss) supported in Power BI, at least for now. so I suggest you to do calculations manually.

You can create a measure, and group data by status(Down, Up), then you can use function left(), MID(), RIGHT() to get each part in duration(format: dd:hh:mm:ss, 4 parts), then use function value() to convert type from string to int. Finally, calculate each part separately and combine 4 parts into one.
FYI:
https://docs.microsoft.com/en-us/dax/text-functions-dax

One quick example (not exactly same, but the logic is similar😃),

https://community.powerbi.com/t5/Desktop/Count-With-Multiple-Conditions-DAX/td-p/1858846

if you have further questions, please let me know.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.