Forum Discussion
Solved
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 wit...
- 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
TomMartens
Super User
Super UserHey,
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
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 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!