Assistance required: Converting seconds measures

[Anonymous]

Hello All,

I needed some assistance. I have gone through the forums but cant seem to find an answer.

I have a measure called run time, which gives me numerical values like 78555,9875,etc.

What I want to do is convert and format this measure to display as  ' X' Days 'Y' Hours

For example if run time is 90,000. It should display as : 1 Day 1 Hours

Using simple math to do this conversion where in one day has 86,400 seconds and an hour has 3,600 seconds.

Please help!! I am stuck at a work project and have spent 2 days trying to figure this out.

[Greg_Deckler · Accepted solution]

See if this article helps. https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486. But you probably want something along the lines of (as a column):

Column = 
VAR __secondsInDay = 86400
VAR __secondsInHour = 3600
VAR __days = INT([Value]/__secondsInDay)
VAR __hours = INT(([Value]-(__days*__secondsInDay))/__secondsInHour)
RETURN
__days & " Days " & __hours & " Hours"

[Greg_Deckler · Accepted solution]

See if this article helps. https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486. But you probably want something along the lines of (as a column):

Column = 
VAR __secondsInDay = 86400
VAR __secondsInHour = 3600
VAR __days = INT([Value]/__secondsInDay)
VAR __hours = INT(([Value]-(__days*__secondsInDay))/__secondsInHour)
RETURN
__days & " Days " & __hours & " Hours"

[Anonymous]

Hi @Greg_Deckler , how would you do this in dax if you were to include minutes and seconds as well? So, day, hour, minute, second.

Best,

Nils

[Anonymous]

Thank You so Much Greg! It does what I want to do but it seems I under estimated the complexity of my end goal. I have a bar chart with the run time in seconds where I wanted to feed this into. I have scaled the bar chart by dividing the run time by 86400, so that it gives me a scale in days(be it decimal), But if I try to put this calculated column as a tooltip it wont give me the days and hours after summing all the run times on that day.

Any workaround for that? I'm thinking of changing the calculation so that instead of taking the 'enabled' measure, I have to create a new measure(sum of all runtimes for that particular date)  which feeds into the below code.

Column 5 = VAR __secondsInDay = 86400
VAR __secondsInHour = 3600
VAR __days = INT('arms_reach vw_mold_tracking'[Enabled]/__secondsInDay)
VAR __hours = INT(([Enabled]-(__days*__secondsInDay))/__secondsInHour)
RETURN
__days & " Days " & __hours & " Hours"

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Rather than create a calculated column, you could new a measure instead still with above formula.

Measure = VAR __secondsInDay = 86400
VAR __secondsInHour = 3600
VAR __days = INT('arms_reach vw_mold_tracking'[Enabled]/__secondsInDay)
VAR __hours = INT(([Enabled]-(__days*__secondsInDay))/__secondsInHour)
RETURN
__days & " Days " & __hours & " Hours"

Best regards,

Yuliana Gu

[Anonymous]

Thank You so Much Greg! It does what I want to do but it seems I under estimated the complexity of my end goal. I have a bar chart with the run time in seconds where I wanted to feed this into. I have scaled the bar chart by dividing the run time by 86400, so that it gives me a scale in days(be it decimal), But if I try to put this calculated column as a tooltip it wont give me the days and hours after summing all the run times on that day.

Any workaround for that? I'm thinking of changing the calculation so that instead of taking the 'enabled' measure, I have to create a new measure(sum of all runtimes for that particular date)  which feeds into the below code.

Column 5 = VAR __secondsInDay = 86400
VAR __secondsInHour = 3600
VAR __days = INT('arms_reach vw_mold_tracking'[Enabled]/__secondsInDay)
VAR __hours = INT(([Enabled]-(__days*__secondsInDay))/__secondsInHour)
RETURN
__days & " Days " & __hours & " Hours"

[Greg_Deckler]

Right, so you want the measure form, which should be something like:

Column = 
VAR __secondsInDay = 86400
VAR __secondsInHour = 3600
VAR __days = INT(SUM([Value])/__secondsInDay)
VAR __hours = INT((SUM([Value])-(__days*__secondsInDay))/__secondsInHour)
RETURN
__days & " Days " & __hours & " Hours"