Forum Discussion
Hours to working days
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
- 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 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
4 Replies
- TomMartens
Super User
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,
TomHi 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!
v-frfei-msftCommunity Support
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
Thanks Frank, exactly what I needed!
