topic Re: Do not account for weekends when calculating "Planned plant shut-down" in DAX Commands and Tips

Do not account for weekends when calculating "Planned plant shut-down"

bandrade — Fri, 30 Aug 2024 16:24:05 GMT

Hello,

I'm trying to calculate the planned plant shutdown for a period range, however, it's still pulling values for Saturday and Sunday, which should not be included in the calculation.

Planned plant shut-down =

VAR WeekDayNumber = SELECTEDVALUE('Calendar'[Week Day Number])

VAR PlantOpen =

IF(

WeekDayNumber = 6 || WeekDayNumber = 7,

CALCULATE(

[Plant Open],

'Calendar'[Open Closed] = "Open"

)

VAR HoursinDay = 24

RETURN

(HoursinDay*[Number of Days]-PlantOpen)*SUM('KAKO Capacity Header Segment'[Number of individual capacities])

Plant Open =

VAR WeekDayNumber = SELECTEDVALUE('Calendar'[Week Day Number])

VAR PlantOpen =

CALCULATE(

SUM('Shift Schedule'[Shift Duration]),

'Shift Schedule'[Day of the Week] = 'Shift Schedule'[Day of the Week],

'Calendar'[Open Closed] = "Open"

)

RETURN

IF(

WeekDayNumber = 6 || WeekDayNumber = 7,

PlantOpen

)

From January 1st, to the 5th, the planned plant shut-down should be 40 (24 hours for monday, and 4 hours for the rest of the week). From January 5th to the 8th, the planned plant shut-down shoud be only 8 hours, 4 hours for each day, and do not account for 24 for saturday and another 24 for sunday.

Could you please assist?

Re: Do not account for weekends when calculating "Planned plant shut-down"

Greg_Deckler — Fri, 30 Aug 2024 17:43:55 GMT

bandrade So typically I use WEEKDAY with a 2 as the second parameter and filter out anything > 5. But, hard to know with the information provided.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Do not account for weekends when calculating "Planned plant shut-down"

lbendlin — Fri, 30 Aug 2024 17:48:51 GMT

Remember there is also a NETWORKDAYS function that you may want to consider.