Forum Discussion

janisCNH's avatar
janisCNH
Frequent Visitor
10 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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]
greggyb's avatar
greggyb
Icon for Resident Rockstar rankResident Rockstar
10 years ago

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.