Forum Discussion
Problem with duration
Brute force:
Measures:
MinutesPerHour = 60 MinutesPerDay = 1440 MinutesPerWeek = 10080
Custom Columns:
Weeks = ROUNDDOWN([Minutes]/[MinutesPerWeek],0) Days = ROUNDDOWN(MOD([Minutes],[MinutesPerWeek])/[MinutesPerDay],0) Hours = ROUNDDOWN(MOD(MOD([Minutes],[MinutesPerWeek]),[MinutesPerDay])/[MinutesPerHour],0) MinutesLeft = ROUNDDOWN(MOD(MOD(MOD([Minutes],[MinutesPerWeek]),[MinutesPerDay]),[MinutesPerHour]),0) Duration = IF(LEN([Weeks])=2,"",0) & [Weeks] & ":" & IF(LEN([Days])=2,"",0) & [Days] & ":" & IF(LEN([Hours])=2,"",0) & [Hours] & ":" & IF(LEN([MinutesLeft])=2,"",0) & ":" & [MinutesLeft]
Community ChampionBrute force:
Measures:
MinutesPerHour = 60 MinutesPerDay = 1440 MinutesPerWeek = 10080
Custom Columns:
Weeks = ROUNDDOWN([Minutes]/[MinutesPerWeek],0) Days = ROUNDDOWN(MOD([Minutes],[MinutesPerWeek])/[MinutesPerDay],0) Hours = ROUNDDOWN(MOD(MOD([Minutes],[MinutesPerWeek]),[MinutesPerDay])/[MinutesPerHour],0) MinutesLeft = ROUNDDOWN(MOD(MOD(MOD([Minutes],[MinutesPerWeek]),[MinutesPerDay]),[MinutesPerHour]),0) Duration = IF(LEN([Weeks])=2,"",0) & [Weeks] & ":" & IF(LEN([Days])=2,"",0) & [Days] & ":" & IF(LEN([Hours])=2,"",0) & [Hours] & ":" & IF(LEN([MinutesLeft])=2,"",0) & ":" & [MinutesLeft]
arifyMicrosoft Employee
Sorry, I'm a bit new in these forums, questions are always answered in DAX. Would it be pointless for me to help people with M code? Because M has nice ways of handling durations
- Greg_Deckler
Not at all, greggyb often answers in M. As an end-user focused tool, DAX tends to be the go to for many of the users of Power BI but M code solutions are just as viable.
- arify
Thanks @smoupre, in that case I'll also put an M response
In M, if you have the number of minutes, you can create the duration value like this (parameters are days, hours, minutes, seconds):
#duration(0, 0, [Minutes], 0)
And then, when you have the duration value, you can get the text representation you wanted like this:
#"Changed Type" = Table.TransformColumnTypes(Source,{ {"DurationInMinutes", Int64.Type} }),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each #duration(0, 0, [DurationInMinutes], 0)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "DurationWithWeeks",
each Number.ToText(Number.IntegerDivide(Duration.Days([Duration]),7)) & ":"
& Number.ToText(Number.Mod(Duration.Days([Duration]),7)) & ":"
& Number.ToText(Duration.Hours([Duration])) & ":"
& Number.ToText(Duration.Minutes([Duration])))
in
#"Added Custom1"Which looks like this:
However, if you're ok with the representation of "Duration" column, it's even simpler
:#"Changed Type" = Table.TransformColumnTypes(Source,{ {"DurationInMinutes", Int64.Type} }), #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each #duration(0, 0, [DurationInMinutes], 0)) in #"Added Custom"
- DominikPetri
Advocate V
During import, divide the minutes by 1400 (e.g. during import). That will result in the fraction of a day (e.g. 0.41666 = 60 minutes) because a day has 1400 minutes (60 minutes times 24 hours).
Using M-code:
Store this value in a new column "Days". Then add a new custom column using Duration.From([Days]). 5000 minutes will result in 3:11:20 (3 days, 11 hours and 20 minutes). If you need parts of this duration, use Duration.Days(), Duration.Hours() etc.
Best regards,
Dominik.
