Algorithm Help

[AppleMan]

Hi,

I have an interesting question I need help figuring out. 

I will start with the overall idea of what im doing. I am taking data from an ERP system that says what days machines can run and how many hours per day. I am using this to create a schedule of load in BI. 

To make this example simple to explain we will be looking at a single machine and my intended result, we will call this machine A.

The data for how the machine schedule looks is below (with calendarID being the machine name):

I am taking this data in the following measure to show the total overall capacity of the machine in a given week (or weeks).

-- For the purpose of this example, assume that weeklyhoursfull = 300 (this is the 100 in the above table * number of machine A's)

-- Also assume countweeksselect is 1.

-- This VAR results in 300. 

VAR _Hours = (SUM(uv_ProductionCalendar[WeeklyHoursFull]) * [CountWeeksSelect])

-- Since the above data example has 0 for saturday and sunday the daysunavailable will result in 2.

VAR _DaysUnavailable = CALCULATE(7 - (SUM(uv_ProductionCalendar[WorkWeek1]) + SUM(uv_ProductionCalendar[WorkWeek2]) + SUM(uv_ProductionCalendar[WorkWeek3]) + SUM(uv_ProductionCalendar[WorkWeek4]) + SUM(uv_ProductionCalendar[WorkWeek5]) + SUM(uv_ProductionCalendar[WorkWeek6]) + SUM(uv_ProductionCalendar[WorkWeek7])))

-- Days total will just result in 7 - 2 or 5 days available for this machine.

VAR _DaysTotal = DATEDIFF(MIN('Date Table'[Date]), MAX('Date Table'[Date]) + 1, DAY) - (_DaysUnavailable * [CountWeeksSelect])

-- Days remaining would be 7 on Sunday, 6 on Monday, etc. 

VAR _DaysRemaining = IF(TODAY() > MIN('Date Table'[Date]) && TODAY() < MAX('Date Table'[Date]) + 1, DATEDIFF(TODAY(), MAX('Date Table'[Date]) + 1, DAY), DATEDIFF(MIN('Date Table'[Date]), MAX('Date Table'[Date]) + 1, DAY)

-- This takes the 300 hours from above, divides it by the 5 available days to show there are 60 hours available per day. Then multiples it by the days remaining in the week. 

VAR _Result = (_Hours / _DaysTotal) * _DaysRemaining

Return _Result

This algorithm works perfectly except one element. I cant figure out how to account for the unavailable days in a week dynamically. So in the above example VAR _Result will equal 360 ((300 / 5) * 6). Where as I need it to be multiply by 5 instead since saturday is an inactive day. Once its tuesday it should be the same calculation, except multiplied by 4, and so on.

Further, if a machine was inactive on say Wednesday, it would need to be accounted for there as well. 

I am trying to logic the best way to take VAR _DaysRemaining and remove inactive days in a way the handles any variable amount of days available or not.

Please let me know if further elaboration is needed, I appreciate the help. 

[AppleMan]

This code is close to accomplishing what I would like (which as of Monday would be returning 1 for the last unavailable day being Saturday), however it is returning 2. The line causing it to return an extra value is for weekday 3, which makes no sense looking at the data.

VAR _UnavailableDaysRemaining =

    CALCULATE(

        COUNTROWS('Date Table'),

        FILTER(

            'Date Table',

            'Date Table'[Date] >= _StartDateRemaining

                && 'Date Table'[Date] <= _EndDate

        ),

        FILTER(

            'Date Table',

            SWITCH(

                WEEKDAY('Date Table'[Date]),

                1, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek1])) = 0,

                2, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek2])) = 0,

                3, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek3])) = 0,

                4, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek4])) = 0,

                5, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek5])) = 0,

                6, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek6])) = 0,

                7, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek7])) = 0,

                FALSE()

            )

        )

    )

[Anonymous · Accepted solution]

Hi @AppleMan,

Thank you for reaching out to Microsoft Fabric Community Forum.

I used a Switch statement to ensure the context is applied with the condition. Can you try this and let me know

VAR _UnavailableDaysRemaining =
    CALCULATE(
        COUNTROWS('Date Table'),
        FILTER(
            'Date Table',
            'Date Table'[Date] >= _StartDateRemaining
&& 'Date Table'[Date] <= _EndDate
        ),
        FILTER(
            'Date Table',
            VAR _Weekday = WEEKDAY('Date Table'[Date])
            VAR _IsUnavailable = 
                SWITCH(
                    _Weekday,
                    1, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek1]) = 0,
                    2, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek2]) = 0,
                    3, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek3]) = 0,
                    4, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek4]) = 0,
                    5, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek5]) = 0,
                    6, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek6]) = 0,
                    7, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek7]) = 0,
                    FALSE()
                )
            RETURN _IsUnavailable
        )
    )

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu