Forum Discussion

BlastS's avatar
BlastS
Icon for Helper I rankHelper I
7 years ago
Solved

Format Duration int to Datetime D HH:mm:SS

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)    ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
BlastS
Icon for Helper I rankHelper I
7 years ago

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?

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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