Forum Discussion
Helper IFormat 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
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
Helper IHi 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)
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?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.