Display Work order scanned time, against machine capacity

[mmalexander]

Hi,

I am developing a report that will show work order scanned time against machine capacity. The idea is that we would use this to see if we are utilising production machines to their full capacity, based on the time scanned against each work order.

The problem is that the capacity, for each machine, is measured in hours per day, and we only work a half day on Friday and no weekends. See sample data below:

We want to have one page that shows the scanned data vs capacity, for 3 groups (25T, 35T & Laser). I was able to do this using 3x gauge visuals, and creating 2 measures (Totals yesterday & total daily capacity). I then added the visual 3 times and filtered each one for the resource group required. I wrote some code to filter out weekend data, but it still looks at Fridays as being a full day capacity

Totals Yesterday =

CALCULATE(

    [Total Time],

    'Date Table'[Date] =

    SWITCH(

    TRUE,

    WEEKDAY(TODAY(), 1) = 1, TODAY() -2,

    WEEKDAY(TODAY(), 1) = 2, TODAY() -3,

    TODAY() - 1

    ))

Total Daily Capacity = SUM(Resources[Total Daily Capacity])

I then look at the previous 4 weeks production, and capture that in 4x column chart visuals. I created a column, in the resource table that calculated the weekly capacity for each machine (Daily capacity x 5), but it does not account for the half day on Fridays. I created 4x measures for each of the previous 4 weeks production and filtered on the resource group. This all works, except for the half day Friday capacity

Week -1 =

CALCULATE(

    [Total Time],

    'Date Table'[Week Number] = WEEKNUM(TODAY(), 21) -1, 'Date Table'[Year] = YEAR(TODAY()))

I now need to show the production for the current year, against capacity, filtered by month and by quarter. In addition to this, I need to show the production for the previous 4 years, against capacity, and filtered by quarters. This is on the third page of the report, and it’s what I’m really struggling to achieve, because I can’t dynamically calculate what the capacity would have been over certain date ranges (Previous years and quarters)

Unfortunately I can't attach a copy of the Power BI report, as I don't see anywhere that lets me.

Thanks in adcance.

[v-karpurapud]

Hi @mmalexander 

We have not yet received a response from you regarding your query. If the previous response was helpful, please let us know. If not, feel free to provide more details so we can assist you further.

Thank you.

[ABD128 · Accepted solution]

Hi @mmalexander 

The issue arises because the current capacity calculation is static and stored only once per resource, meaning it cannot dynamically adjust for different date ranges, account for the half-day worked on Fridays, or exclude weekends. This results in inaccurate values when attempting to compare actual scanned time against available capacity for months, quarters, or years. To address this, the Date Table should be enhanced with a WorkingDayType column that classifies each date as “Full Day,” “Half Day,” or “Weekend.” This classification can then be used in a dynamic DAX measure to accurately compute expected capacity for any given period while respecting Resource Group filters. For example, the following calculated column in the Date Table will define working day types:

WorkingDayType =

SWITCH(

TRUE(),

'Date Table'[Day Name] IN {"Saturday", "Sunday"}, "Weekend",

'Date Table'[Day Name] = "Friday", "Half Day",

"Full Day" )

Once this classification exists, a dynamic measure can be created to calculate expected capacity across the selected period, correctly applying a 0.5 multiplier for half-day Fridays and ignoring weekends:

Expected Capacity =

VAR FullDays =

CALCULATE( COUNTROWS('Date Table'),

'Date Table'[WorkingDayType] = "Full Day" )

VAR HalfDays =

CALCULATE(

COUNTROWS('Date Table'), '

Date Table'[working] = "Half Day" )

RETURN

SUMX(

VALUES(Resources[Resource ID]),

Resources[Daily Capacity] * FullDays +

(Resources[Daily Capacity] * 0.5 * HalfDays) )

This ensures that capacity is calculated per resource in the current filter context, so Resource Group filtering will work correctly. The scanned production time can be measured with:

Total Scanned Time =

SUM('Resource Time Receipt'[Hours])

By using these measures in a clustered column chart with the Date Table’s Month or Quarter on the axis, Resource Group as the legend, and both Total Scanned Time and Expected Capacity as values the report will display accurate side-by-side comparisons of actual versus available capacity for any period.

Regards,

ABD.

[mmalexander]

Hi ABD128,

This worked great. Thanks for all your help

[v-karpurapud]

Hi @mmalexander 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[mmalexander]

It's not resolved yet

Basically I want to show the total scanned time, and expected capacity, for each resource Group over a given time period. I want to display this on a column chart, with a coulmn for each value showing the quarterly or monthly numbers next to each other (Like the image below)

[v-karpurapud]

Hi @mmalexander 

Thank you for posting your question on the Microsoft Fabric Community Forum, and thanks as well @amitchandak , @FBergamaschi and @ABD128  for the ongoing support and assistance.

Could you let us know if the suggested solution resolved your issue? This information can assist other community members facing similar challenges.

Thank you.

[ABD128]

Hi,

The current issue stems from using static logic to calculate machine capacity, which does not accurately reflect the organization's working schedule. While the data model is correctly structured with appropriate relationships between the Resource Time Receipt, Resources, and Date Table, the implementation overlooks reduced hours on Fridays and excludes weekends. This results in overstated capacity figures and inaccurate comparisons with actual scanned production time, especially in weekly, monthly, and yearly visuals. Additionally, the gauge visuals are returning blank values because the DAX measures do not properly respond to visual-level filters or correctly identify the previous working day. To address these issues, it is recommended to enhance the Date Table with a WorkingDayType column to classify each date as a full day, half day, or weekend. A dynamic capacity measure should then be implemented to accurately calculate expected hours for any selected date range, using a 0.5 multiplier for Fridays and excluding weekends. The daily scanned time and capacity measures should be revised with CALCULATE() and contextual date logic to ensure accuracy when filtered by resource group. Finally, all time-based visuals should be updated to use this dynamic measure, providing a consistent and accurate representation of machine utilization aligned with actual production schedules.

Best Regards,

ABD.

[mmalexander]

I've tried putting the capacity into the date table, but it does not filter for each Resource Group. It only adds up all the capacity and assigns it to each group (effectively summing the total capacity and adding it to each group)

Also the blank gauge visuals are because you don't have any data loaded for the previous day (As you don't have a gateway into our network/database). The gauge visuals work perferctly when the data is refreshed.

I have the previous day visuals and the previous 4 week visuals working ok (Without the half day Friday accounted for). It's when I try to extend the time period to previous years/quarters/months then I cannot get it owrking because the capacity is not a running sum, but a single value only stored once.

[FBergamaschi]

Can you please share the pbix via some free cloud (google drive or similars) and paste the link here ?

That would help supporting you

You created a very nice post but now please can you go straight to what you need. I understood that:

1 - you need to half the capacity of fridays (in your dat ayou cannot change the capacity for that day), so all this need to be done in a measure

2 - you need to calculate the capacity in the past

Is the above list correct? Please share the model so we can fix it

Thanks

[mmalexander]

Hi,

See link to dropbox file location, with the pbix

file.https://www.dropbox.com/scl/fi/xj36n93it1i2v36a4m8iy/Resource-Time-Tracking-2.pbix?rlkey=nakf98edlr6... 

[amitchandak]

@mmalexander , If it based on selection you can use 

CALCULATE(

[Total Time], sameperiodlastyear(Date[Date]) )

or

CALCULATE(

[Total Time], dateadd(Date[Date],-1, year) )

2nd last year = 

CALCULATE(

[Total Time], dateadd(Date[Date],-2, year) )

and so on 

Same way last day is

CALCULATE(

[Total Time], dateadd(Date[Date],-1, day) )

based on today

Today = CALCULATE([Net], FILTER('Date','Date'[Date] = Today() ) )
Yesterday = CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-1 ) )
Same day Last week Today= CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-7 ) )
Month Start Date Today = CALCULATE([Net], FILTER('Date','Date'[Date] = Eomonth(Today(),-1)+1 ) )
Month End Date Today = CALCULATE([Net], FILTER('Date','Date'[Date] = Eomonth(Today(),0) ) )
Last Month End Date Today = CALCULATE([Net], FILTER('Date','Date'[Date] = Eomonth(Today(),-1) ) )
Last Month End Date Today = CALCULATE([Net], FILTER('Date','Date'[Date] = Eomonth(Today(),-1) ) )
Last year same WeekDay = CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-364 ) )
Last year same date = CALCULATE([Net], FILTER('Date','Date'[Date] = date(Year(Today())-1, month(Today()), day(Today()) )))

[mmalexander]

Hi, Sorry for the late reply, I was on vacation.

I don't have an issue calculating the total time. The problem is I want to show the total time, against the capacity for a given time period. I'd like to have a column graph where I can show time scanned and capacity for the same time period, and when I alter the date range it will automatically update.

I can do this for the total time, as it's  constantly being updated and added to the Resource Time Receipt table, but the capacity is only entered once in the Resource table