Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bkwohls
Helper I
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 NumberCellPart #JobStartDateEstHours
1234a1x1239/3/202117.5
998b1x5579/3/202123
887c23wec9/1/20217
665d23det9/2/202111

 

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

Job NumberCell9/1/20219/2/20219/3/20219/4/20219/5/20219/6/20219/7/2021
1234a1  8 Skip WE Skip WE81.5
998b1  8 Skip WE Skip WE87
887c27   Skip WE Skip WE  
665d2 83 Skip WE Skip WE  

 

Final Desired Result:

Cell9/1/20219/2/20219/3/20219/6/20219/7/2021
1  16161.5
278387
1 ACCEPTED SOLUTION

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:

5.png

Regards,
Xiaoxin Sheng

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

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
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:

Before You Post, Read This 

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.

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.

 

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:

5.png

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
daxer-almighty
Solution Sage
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

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.