Forum Discussion
Format Duration int to Datetime D HH:mm:SS
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
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.
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:
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.
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?
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
