Format Duration int to Datetime D HH:mm:SS

[BlastS]

Hi, i am strungligh to get this formula, since i converted my datetime to duration and now i would like to Sum the duration and the outputformat would be D HH:mm: SS (Days Hours Minutes Seconds) 

Format of DateTime Column = DD/MM/YYYY hh:mm: SS 

(example 02/02/2002 20:30:00)

(example 02/02/2002 20:50:00)

...

Formula to get Duration From my DateTime Column: 

Duration = IF(DATEDIFF(LOOKUPVALUE(TableX[DateTime],TableX[Index],TableX[Index]-1),TableX[Index],MINUTE)<-1,BLANK(),DATEDIFF(LOOKUPVALUE(TableX[DateTime],TableX[Index],TableX[Index]-1),TableX[DateTIme],MINUTE))

My duration column got values like this

10

20

10

10

10

10

10

10

10

10

...

Now i can SumDuration and Set format like this (DD HH:mm: SS) is there a easy to do this ?

[Anonymous]

I needed the H:mm datatype to represent a duration derived from subtracting Date/time types, but when I tried to use that datatype to create a calculated column-eekkk yikes, Power BI gave me the wrong data.  Segments of the 24th hr or something, Work. So I went back to my excel file, made the calculated column from there,  re-uploaded and like magic, no datatype conversion issues. 

---

---

---

[Anonymous]

Create a new blank query and pop this code in (The source line is just an Enter Data).  This should give you an idea how to convert Minutes into Duration

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJQitWJVjKGUKZAKhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "AsTime", each [Time] / 24 / 60),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"AsTime", type duration}})
in
    #"Changed Type1"

You could select "DateTime" instead of duration if you wished.

[BlastS]

Hi @Anonymous

I am not truly understand how can i do this with the code you provide, on source speacialy.

I need to create new "SubTable" on my powerquery ? and somehow apply those steps?

If possible could you provide a example in a pbix file ?

Best regards,

Blasts

[Anonymous]

The source line just sets up the sample data.  If you click through the applied steps, you'll get an idea of how i'm transforming the data into the new column.  The idea is that you can take the method and apply it to your existing tables.

Really all i'm doing is taking your minutes as a whole number and dividing them by 60 and by 24.  This gets you a number in the format that is expected by Duration.  Duration is in a format where a whole day is 1.  Therefore half a day (12 hours) is 0.5.  To convert minutes as a whole number, you divide by 60 and divide again by 24.

[BlastS]

@Anonymous

I  did a few trys on source and didnt work out, i tryed for example put the source = to TableA.DaTeTime and others trys and no info show. (My duration column was created outside powerQuery and for that so i will only have DateTime column to work with)

I clearly understand the nexts steps that i need to do after the source, seems logical and easy but this one i am stuck, and for what i understand the source is like my input still probably i am writing it wrong dont know.

Sorry bother you again but if you can help to define the source i would apreciate it very much.

[Anonymous]

Power Query has to happen first, so if your duration column was being created afterward, in DAX, then you'll need to work out a different method of getting that data inside Power Query.

Alternatively, if thats not possible, you could create this column in DAX instead using a similar idea.  Try adding a new column and going:

YourNewDurationColumn = DIVIDE([YourOldColumn], 1440)

Note:  Dividing by 1440 is the same as dividing by 60 then by 24

[BlastS]

Hi @Anonymous

I am using Dax Right now its the betteer way in my case to try do this.

Okay got this column now, should i sum(when columnX = 0,5)"its a condition i need to filter which duration should i get, and next  need convert  to outputformat (D HH;MM:SS) 

My question here is how can i based on this numbers convert in DAX to that specific datetime (i already tryed to check on modeling and didnt find out nothing nearly)

[Anonymous]

The first thing to consider is how a value is stored and how a value is displayed are 2 different concepts.  So far we have been converting the data so it is stored within the correct format.  This format will allow you to do all of the duration math you need, so as summing up the values or making adjustments to an existing DateTime value.

So the question you need to answer is, the values in this new column, how will they be surfaced on a report?  Do you want to sum up all your durations and give a resulting figure?  Are you looking to take some existing Dates and Times and use the Duration to move them forward/backwards?

These questions will help you understand how to format the results.

[BlastS]

@Anonymous

Looking to the question , yes i want to sum all the duration within a specific condition and then format.

The problem i get is when i sum all the NewDuration column it only returns 0 , 1 .

Formula: 

SumNewDurationINConditoon= CALCULATE(SUM(TableX[NewDuration]),TableX[ColumnB)]=.50) 

This only returning 0 1 i think the formula is correct since sum all the duration when the other column is 0.5, what i am doing wrong?

[Anonymous]

Check if you've set the data format of the new measure to be a whole number or a decimal. It should be decimal.

Also make sure that the SumNewDurationInConditioon is a measure.  I'd expect it will be, but was just worth checking in case you made it a calculated column by accident.

[BlastS]

@Anonymous

I had as whole number already changed to decimal now i got the totalof duration but i still need to convert it to Type Duration or Time in DAX i get a number right now like

I need to Divide This number by Another ColumnY and after need to convert to duration or time in DAX how can i do it in DAX?

[v-cherch-msft]

Hi @BlastS

Here is the similar post for your reference.

https://community.powerbi.com/t5/Desktop/Convert-seconds-column-into-duration-column-hh-mm-ss-ss-nee...

Regards,

Cherie

[Anonymous · Accepted solution]

Just to make sure everything is in order.  Your original field, which expressed the data in Minutes as a whole number, will be of data type 'Whole Number', but all subsequent fields should be either a decimal number type, or a date/time style number type.

Duration is only a datatype within Edit Queries (Power Query Language, known as M), but is not a data type you can select in the Power BI Data Model itself.  Duration is best kept as a decimal number, but you can format it yourself when trying to display on a report.  This could be done using another measure and the FORMAT statement, or by using some math tricky to build out a TEXT value to display how you wish.

For example:

Display Duration = VAR hours = FLOOR([SumNewDurationINConditoon] * 24, 1)
var minutes = FLOOR(([SumNewDurationINConditoon] * 24 * 60) - (hours * 60), 1)
RETURN
hours & ":" & minutes