cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Determining Manufacturing Schedule Loaded per cell

I have a very simple Data Source that tells me the Job, Start Date, Cell it will be manufactured on, and the estimated hours. I need to aggregate this Data into a simple calendar of Total hours by cell by Day. Is this possible? Thanks in advance

Jobs Table

 Job Number Cell Part # JobStartDate EstHours 1234a 1 x123 9/3/2021 17.5 998b 1 x557 9/3/2021 23 887c 2 3wec 9/1/2021 7 665d 2 3det 9/2/2021 11

I am guessing this would be the first intermediate Step??

 Job Number Cell 9/1/2021 9/2/2021 9/3/2021 9/4/2021 9/5/2021 9/6/2021 9/7/2021 1234a 1 8 Skip WE Skip WE 8 1.5 998b 1 8 Skip WE Skip WE 8 7 887c 2 7 Skip WE Skip WE 665d 2 8 3 Skip WE Skip WE

Final Desired Result:

 Cell 9/1/2021 9/2/2021 9/3/2021 9/6/2021 9/7/2021 1 16 16 1.5 2 7 8 3 8 7
1 ACCEPTED SOLUTION
Community Support

Hi @bkwohls,

You can create a new table as calendar to store the date values based on the raw table, then write a measure to calculate datediff between the start date and the calendar date to get daily work hours.

After these steps, you can use the raw table category and new table date, measure to create the matrix visuals.

Sample formulas:

Calculated table.

``````Calendar =
CALENDAR (
MINX ( 'Table', [JobStartDate] ),
MAXX ( 'Table', [JobStartDate] ) + 365
)``````

Measure:

``````Measure =
VAR totalHour =
CALCULATE (
SUM ( 'Table'[EstHours] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Job Number] ),
VALUES ( 'Table'[Part #] )
)
VAR cDate =
MAX ( 'Calendar'[Date] )
VAR jobStart =
CALCULATE (
MAX ( 'Table'[JobStartDate] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Job Number] ),
VALUES ( 'Table'[Part #] )
)
RETURN
IF (
HASONEVALUE ( 'Table'[Job Number] ),
IF (
cDate >= jobStart
&& WEEKDAY ( cDate, 2 ) <= 5,
VAR diff =
totalHour
- (
COUNTROWS (
FILTER ( CALENDAR ( jobStart, cDate ), WEEKDAY ( [Date], 2 ) <= 5 )
) - 1
) * 8
RETURN
IF ( diff >= 0, MIN ( MAX ( diff, 0 ), 8 ) )
)
)``````

Result:

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
5 REPLIES 5
Community Support

Hi @bkwohls,

It seems like a common analysis requirement about two date fields, you can take a look at the following link start/end dagte part if help:

In addition, you can also try to create a new table to expand and the date ranges and link to raw table records then you can simply aggregate these records from the date ranges:

Solved: Spread revenue across period based on start and en... - Microsoft Power BI Community

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper I

I only have a start date and then the hours that are estimated. I am essentially trying to calculate an end date based on an 8 hour work day given the Start date and hours required. With that info then I can aggregate all the hours per day and evaluate resource load per day per manufacturing cell.

Community Support

Hi @bkwohls,

You can create a new table as calendar to store the date values based on the raw table, then write a measure to calculate datediff between the start date and the calendar date to get daily work hours.

After these steps, you can use the raw table category and new table date, measure to create the matrix visuals.

Sample formulas:

Calculated table.

``````Calendar =
CALENDAR (
MINX ( 'Table', [JobStartDate] ),
MAXX ( 'Table', [JobStartDate] ) + 365
)``````

Measure:

``````Measure =
VAR totalHour =
CALCULATE (
SUM ( 'Table'[EstHours] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Job Number] ),
VALUES ( 'Table'[Part #] )
)
VAR cDate =
MAX ( 'Calendar'[Date] )
VAR jobStart =
CALCULATE (
MAX ( 'Table'[JobStartDate] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Job Number] ),
VALUES ( 'Table'[Part #] )
)
RETURN
IF (
HASONEVALUE ( 'Table'[Job Number] ),
IF (
cDate >= jobStart
&& WEEKDAY ( cDate, 2 ) <= 5,
VAR diff =
totalHour
- (
COUNTROWS (
FILTER ( CALENDAR ( jobStart, cDate ), WEEKDAY ( [Date], 2 ) <= 5 )
) - 1
) * 8
RETURN
IF ( diff >= 0, MIN ( MAX ( diff, 0 ), 8 ) )
)
)``````

Result:

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Solution Sage

Are you talking about a calculated table? A measure? Do you want to do this in Power Query? Or in PBI Desktop? Why are you pivoting data on date? This is not a format suitable for PBI modeling. Any particular reasons? Nothing is really clear about what you're trying to achieve...

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Helper I

My data source for Jobs data only provides me with a Start Date and a Estimate number of hours to complete the Job. Based on an 8 hour day and skipping weekends I want to be able to calculate how amny hours per day I have in teh Job Schedule.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.