- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate production hours per week
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"
Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
05-17-2024 01:30 PM | |||
03-31-2024 12:51 PM | |||
10-09-2024 02:21 PM | |||
09-26-2024 03:26 AM | |||
08-05-2024 01:51 AM |
User | Count |
---|---|
23 | |
12 | |
10 | |
9 | |
8 |
User | Count |
---|---|
16 | |
15 | |
15 | |
12 | |
10 |