How to calculate work day hours into a DAX formula with a data field

[niemis]

I have a data field titled 'Duration_mins' which calculates the actual time spent on a task, not from open to close.  This field is directly imported into Power BI via ServiceNow.  ServiceNow calculates time worked as follows:

1 business day = 480 minutes (one 8 hour day)

5 business days = 2400 minutes (five 8 hour days)

10 business days = 4800 minutes (ten 8 hour days).

I need to create a calculation that will take the 'duration_mins' field and somehow end up with business days.  So if I have a total of 4535.93 minutes, it would show 9.44 business days.  

[v-weiyan1-msft]

Hi @niemis ,

You can use the DIVIDE function as described in the method provided by @VahidDM .
You can also consider the following methods to solve the problem.
My Sample:

Based on your description, Please try code as below to create a measure.

Business Days = ROUNDDOWN((SELECTEDVALUE('Table'[Duration_mins]) / 480),2)

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[niemis]

Hello!  Thank you.  I did try formula as you suggested, but this is what I received:

[v-weiyan1-msft · Accepted solution]

Hi @niemis ,

From the screenshot you provided, it appears that your formula is missing a bracket, which is causing the error.

The formula is shown below:

Business Days = ROUNDDOWN((SELECTEDVALUE('Table'[Duration_mins]) / 480),2)

Please try to check the result after adding the parentheses at the position shown by the arrow in the screenshot below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[niemis]

Thank you so very much for your help with this!!!!  It worked great!  I really need to learn the formulas and what they do.  Thanks again!

[VahidDM]

To calculate business days based on the 'Duration_mins' field in Power BI using DAX, you need to create a measure that divides the duration in minutes by the number of minutes in a business day. In your case, one business day is defined as 480 minutes. The formula will convert the total duration in minutes into business days.

Here is how you can create the DAX formula:

1. Measure Creation: Go to the Power BI report view, and create a new measure. This is done by right-clicking on your table in the fields pane and selecting "New measure".

2. DAX Formula:

   Business Days = DIVIDE([Duration_mins], 480, 0)

   This formula divides the 'Duration_mins' by 480 (the number of minutes in a business day) and uses 0 as an optional argument for the divide function to handle division by zero scenarios.

3. Formatting the Measure: You may want to format this measure to show decimal places as it represents days. You can format the measure to show up to two decimal places to represent partial days.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[niemis]

Hello VahidDM and thank you.  However, my data fields are in a summarized format, and when I go to add them to the formula you provided, they aren't available.  I tried changing the format, but the result was still the same.