Forum Discussion
Problem with duration
Hello Community!
I wanted to create a report with Power BI but I had a problem. In one column the duration of a feedback is presented in minutes. In some cases the answer needs several days so it would be great if I could transform the duration into "weeks:days:hours:minutes".
It would be really helpfull if somebody may help me because I am new in Power BI :smileyhappy:
Regards Janis
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]
8 Replies
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]
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
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.
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.
There's no duration data type in the Tabular engine. You'd have to manually do the mod arithmetic to determine the number of weeks, days, hours, and minutes an integer represents, and then concatenate those together. You'd end up with a string you can't do any sort of arithmetic on. If that's acceptable, you should be able to get by with INT() MOD() and the division operator.
