Forum Discussion
Hours to working days
- 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
Community SupportHi 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!
