Hours to working days

[juan-botero]

Hello everyone!

I have a measure which shows how long will a project take in hours. These hours are not formatted as time, they are just numbers. I want to convert that number to working days with a 9 hour per day work.

I will appreciate all your help.

Kind regards,

Juan

[TomMartens · Accepted solution]

Hey,

I'm not sure how the result should look like, but I'm sure that need the functions INT() and MOD(). Assuming that there are 21 hours INT(21/9) results to 2, the number of days. MOD(21,9) results to 3, the number of remaining hours.

Here is the link to the MOD function: https://msdn.microsoft.com/en-us/query-bi/dax/mod-function-dax

You also have to consider to encapsulate both functions I mentioned above in the expression part of SUMX to get the correct result for totals.

If you need more help, then provide sample data. Upload a file to onedrive or dropbox and share the link.

Regards,
Tom

[v-frfei-msft · Accepted solution]

Hi @juan-botero,

Based on your description, I made an sample here. At first, I created a measure to get the working hours.

diffhours = DATEDIFF(MAX(Table2[start date]),MAX(Table2[end date]),HOUR)

Then we can get working days by the formula as below.

Measure1 = IF([diffhours]<9,"0 day" & [diffhours], INT([diffhours]/9) & " day(s) " & MOD([diffhours],9) &" hour(s)")

For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share you sample data or your pbix to me.

https://www.dropbox.com/s/dh2d4s0de30e5ih/working%20days.pbix?dl=0

Regards,

Frank

[juan-botero]

Thanks Frank, exactly what I needed!

[TomMartens · Accepted solution]

Hey,

I'm not sure how the result should look like, but I'm sure that need the functions INT() and MOD(). Assuming that there are 21 hours INT(21/9) results to 2, the number of days. MOD(21,9) results to 3, the number of remaining hours.

Here is the link to the MOD function: https://msdn.microsoft.com/en-us/query-bi/dax/mod-function-dax

You also have to consider to encapsulate both functions I mentioned above in the expression part of SUMX to get the correct result for totals.

If you need more help, then provide sample data. Upload a file to onedrive or dropbox and share the link.

Regards,
Tom

[juan-botero]

Hi Tom.

INT and MOD functions were exactly what I needed, but, as stated in the following post, I used the function IF instead of SUMX. Thanks a lot for your help!