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]
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
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]
DominikPetri's avatar
DominikPetri
Icon for Advocate V rankAdvocate V
10 years ago

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.