Issue with accounting for holidays in my workday count for 3 separate locations

[alex20p]

I am having issues accounting for holidays in my workday count for each location.

I currently have 2 fact tables that are joined to a calendar table and the calendar table connected to my holiday table.

In my Holiday table i have a list of dates, holiday name and a location code. I have 3 location codes that are tied to 3 different locations. 1 code per location. Each location has a different set of holidays that they are closed.  For example location code A has 10 different holidays tied to it. location code N has 11 holidays tied to it and code C has 9 holidays tied to it. 

I am trying to create a measure in a matrix that will basically show the count of working days for the week for each location including if there is a holiday. 

For Locaiton N and C they work Mon-FRI(5 days) and M works 7 days a week. If there is holiday i would like to drop my workday number down by 1. 

Currently my measure for workdays looks like this 

WorkingDaysPerWeek = 
VAR SelectedLocation = SELECTEDVALUE(LocationSlicer[Location]) 
RETURN
CALCULATE(
    COUNTROWS('Calendar'),
    FILTER(
        'Calendar',
        (
            
            (SelectedLocation IN {"N", "C"} && 'Calendar'[Day Of Week] > 0 && 'Calendar'[Day Of Week] <= 5)
            ||
       
            (SelectedLocation = "M")
        )
    )
)

The code above is working perfect to show the workdays for each location. My 2 fact tables have the location code in them.  I am having issues tying in the holidays. I have another table called LocationSlicer that is joined to my fact tables that basically acts as a slicer so i can pick my location.(there are multiple codes that tie into a location) 

Any help is appreicated!

[v-kpoloju-msft · Accepted solution]

Hi @alex20p,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @ for your inputs on this issue.

I understand you're working on accurately calculating working days per week for various locations, considering location-specific holidays. You've done an excellent job setting up your calendar, holiday, and slicer tables—you're almost there.

You're correctly counting standard workdays based on each location's working schedule. The next step is to subtract holidays from that count, ensuring they correspond with the selected location. Here’s how you can update your WorkingDaysPerWeek measure to exclude holidays based on the selected location:

WorkingDaysPerWeek =

VAR SelectedLocation = SELECTEDVALUE(LocationSlicer[Location])

RETURN

CALCULATE(

    COUNTROWS('Calendar'),

    FILTER(

        'Calendar',

        (

            // Mon–Fri for N and C

            (SelectedLocation IN {"N", "C"} && 'Calendar'[Day Of Week] > 0 && 'Calendar'[Day Of Week] <= 5)

            ||

            // All days for M

            (SelectedLocation = "M")

        )

    ),

    // Exclude holidays for the selected location

    NOT(

        'Calendar'[Date] IN

        CALCULATETABLE(

            VALUES(Holiday[Date]),

            Holiday[LocationCode] = SelectedLocation

        )

    )

)

This measure filters out holidays based on both the Date and LocationCode in your Holiday table. Make sure there's a relationship between your Calendar table and Holiday table on the Date field, and that Holiday[LocationCode] matches the values used in your LocationSlicer. If the issue still persists,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

If this helps, please consider giving a Kudos or marking it as Accepted Solution to assist others in the community.

Thanks for using the Microsoft Community Forum!

[v-kpoloju-msft]

Hi @alex20p,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[lbendlin]

If you have the list of holidays for each location you can feed that to the NETWORKDAYS function for a simpler solution.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...