topic Re: Calculate production hours per week in DAX Commands and Tips

Calculate production hours per week

Bart1133 — Wed, 08 Jan 2025 13:03:28 GMT

Hi,

I got the request to build a forecast report to show per week how many production hours is planned. Based on the required FTE, we can then evaluatie how many people are required per shift.

Workweek is from Monday 5:00 am to Friday 5:00 am (=120 hours).

In my table NAV_Prod Order Line I have on rowbased the information per production order where I have calculated columns for [Dynamic Start DateTime] and [Dynamic End DateTime].

I have also a measure [Theo. Prod. Time [h]]] which calculate the amount of theoretical hours required to complete the production order.

Example:

Dynamic Start DateTime: 27/02/2025 13:30:00

Dynamic End DateTime: 7/03/2025 9:23:00

[Theo. Prod. Time [h]]] = 138,89

In my visual, i should get a value of 39,5h in week 9 and 99,39h in week 10.

I would not use the [Dynamic End DateTime] but base it on [Dynamic Start DateTime] and the [Theo. Prod. Time [h]]].

I'm unable to make this work. Can someone help me here?

Thank you

Regards,

Bart

Re: Calculate production hours per week

lbendlin — Wed, 08 Jan 2025 16:47:32 GMT

How granular does it have to be? Is minute level sufficient? Does your data model include a holidays table? Are your users all in the same time zone?

Re: Calculate production hours per week

Bart1133 — Thu, 09 Jan 2025 07:00:29 GMT

Hi,

On minute level is indeed sufficient.

Data doesn't include a holidays table and is at this stage not required.

All users are in the same time zone.

See screenshot of how far I got. As you can see, I managed to calculate the "remaining hours". But now I need to get this in the next week. Week 10 in this example.

_RemainingHours (OK) =

VAR WeekStartDateTime =

CALCULATETABLE(

VALUES('NAV_Prod Order Line'[Dynamic Start DateTime]),

'NAV_Prod Order Line'[Inventory Posting Group] = "EINDPROD."

)

VAR WeekEndDateTime =

DATE(YEAR(WeekStartDateTime),

MONTH(WeekStartDateTime),

DAY(WeekStartDateTime)) + TIME(5, 0, 0) + (6 - WEEKDAY(WeekStartDateTime, 2))

VAR HoursThisWeek =

IF(

WeekStartDateTime <= WeekEndDateTime,

MAX(0, DATEDIFF(WeekStartDateTime, WeekEndDateTime, MINUTE) / 60),

)

VAR CurrentWeek = WEEKNUM(WeekStartDateTime, 2)

VAR RemainingHours =

[Theo. Prod. Time [h]]] -

IF(

WeekStartDateTime <= WeekEndDateTime,

MAX(0, DATEDIFF(WeekStartDateTime, WeekEndDateTime, MINUTE) / 60),

)

VAR Result =

IF(

CurrentWeek = SELECTEDVALUE('Date'[Week]),

RemainingHours,

BLANK() -- Display nothing for non-relevant weeks

)

RETURN

Result

Re: Calculate production hours per week

Poojara_D12 — Thu, 09 Jan 2025 07:12:52 GMT

To build the forecast report for planned production hours per week, based on your data structure, you can approach this by calculating the number of hours in each week using the [Dynamic Start DateTime] and [Theo. Prod. Time [h]]]. Here's how you can approach the problem:

1. Define Week Ranges

First, you need to define the weeks you're evaluating (e.g., starting from Monday 5:00 AM). You can create a Week Column or Week Number from the [Dynamic Start DateTime] to group the data into weeks.

2. Calculate Production Hours for Each Week

To calculate the production hours for each week, you can break down the [Theo. Prod. Time [h]]] based on the [Dynamic Start DateTime]:

For each row (production order), determine how much of the [Theo. Prod. Time [h]]] falls into the week in question.

3. Creating a Measure for Weekly Hours Calculation

Define the start of the week (Monday 5:00 AM): Create a measure that gets the week number based on [Dynamic Start DateTime]:

Week Number = WEEKNUM('Table'[Dynamic Start DateTime],2)

This will assign each Dynamic Start DateTime to a specific week.

Calculate the weekly hours: You'll need a measure that distributes the [Theo. Prod. Time [h]]] into each week based on the [Dynamic Start DateTime]. For this, create a measure to calculate how many hours fall into a given week:

Weekly Prod. Time = VAR WeekStart = DATEADD('Table'[Dynamic Start DateTime], - WEEKDAY('Table'[Dynamic Start DateTime],2) + 1, DAY) VAR WeekEnd = DATEADD(WeekStart, 5, DAY) VAR TotalHours = 'Table'[Theo. Prod. Time [h]]] VAR HoursInCurrentWeek = IF('Table'[Dynamic Start DateTime] >= WeekStart && 'Table'[Dynamic Start DateTime] < WeekEnd, TotalHours, 0) RETURN HoursInCurrentWeek

Distribute the time if the production order spans multiple weeks: If the production order spans over multiple weeks, break down the [Theo. Prod. Time [h]]] proportionally to the weeks involved. For example, if the production order starts on Wednesday in Week 9 and ends on Tuesday in Week 10, the hours will be split across both weeks.
For simplicity, you can calculate the number of hours in each week like this:

Hours Per Week = VAR StartOfWeek = DATEADD('Table'[Dynamic Start DateTime], - WEEKDAY('Table'[Dynamic Start DateTime], 2) + 1, DAY) VAR EndOfWeek = DATEADD(StartOfWeek, 5, DAY) VAR TotalDuration = 'Table'[Theo. Prod. Time [h]]] VAR DurationInCurrentWeek = IF('Table'[Dynamic Start DateTime] >= StartOfWeek && 'Table'[Dynamic Start DateTime] < EndOfWeek, TotalDuration, 0) RETURN DurationInCurrentWeek

4. Grouping by Week and Shift

Now that you have the calculated weekly hours, you can create a summary table or use a Pivot Table visual in Power BI to aggregate the results by week and evaluate how many people are required per shift based on the required FTE.

For example:

Planned FTE per Week: Based on the total hours in each week and the required FTE, calculate how many people are required per shift.
Shift Hours: You can create another measure to calculate the number of people needed per shift.

Ensure your report groups by the week number.
Use the calculated measure for weekly production hours.
Based on the total hours for each week, divide by the number of hours per shift to evaluate the required FTE.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: Calculate production hours per week

Bart1133 — Fri, 10 Jan 2025 06:42:11 GMT

It doesn't give the desired solution.
as mentioned before, I can calculate the remaining hours. Now it only needs to be shown in the colomn of week 10.

since the relationship with the date calender is with the Dynamic Start Date. It always shows it in the week 9 kolom.
I've a inactive relationship with the Dynmic End Date but I'm not confident if it is smart to activate this relationship in the measure.

_NextWeekRemainingHours = VAR WeekStartDateTime = CALCULATETABLE( VALUES('NAV_Prod Order Line'[Dynamic Start DateTime]), 'NAV_Prod Order Line'[Inventory Posting Group] = "EINDPROD." ) VAR WeekEndDateTime = DATE(YEAR(WeekStartDateTime), MONTH(WeekStartDateTime), DAY(WeekStartDateTime)) + TIME(5, 0, 0) + (6 - WEEKDAY(WeekStartDateTime, 2)) VAR CurrentWeek = WEEKNUM(WeekStartDateTime, 2) -- Get the current week VAR NextWeek = IF( SELECTEDVALUE('Date'[Week]) = CurrentWeek, CurrentWeek + 1, BLANK() ) -- Only calculate NextWeek for the next valid week, return BLANK for all other weeks VAR RemainingHours = -- Berekend hoeveel werkuren er nog nodig zijn. [Theo. Prod. Time [h]]] - IF( WeekStartDateTime <= WeekEndDateTime, MAX(0, DATEDIFF(WeekStartDateTime, WeekEndDateTime, MINUTE) / 60), 0 ) VAR ResultRemainingHours = IF( CurrentWeek = SELECTEDVALUE('Date'[Week]), RemainingHours, BLANK() -- Display nothing for non-relevant weeks ) VAR Result = IF( NextWeek = SELECTEDVALUE('Date'[Week])+1, ResultRemainingHours, BLANK() -- Blank for weeks other than the next one ) RETURN Result

Re: Calculate production hours per week

lbendlin — Fri, 10 Jan 2025 15:02:06 GMT

I use INTERSECT in these scenarios, between your datetime ranges and a reference table that has minute level granularity. You can get that by multiplying both sides with 1440 and then discarding the fractions.

Re: Calculate production hours per week

Bart1133 — Tue, 14 Jan 2025 08:23:04 GMT

I've solved it by putting the calculated hours in calculated columns, aswell as the week numbers. Then created a measure to show the correct data. Maybe not the most efficient method but it works.