Calc Next Working Day excluding weekends and bank holidays

[javirmerino]

Hi Guys, i'm hoping that you can help me out here and potentially advise on some best practices too. I'm brand new to Power BI/M/DAX and relatively new to SQL so my ambition far exceeds my skillset currently!

i've got three tables; an extract from a SQL server which is connected to a dimension table on the received date, and an SLA table connected to the extract based on an urgency. Relationships shown below nb. the inactive relationship between dim_date and the Closed date field also;

Based on the Received Date and Time in the extract, i'm trying to calculate the next working day from dim_date. For example, if the received time is before the start of the working day, then that day is the next working day. However, if it is received after 5pm or on a weekend then the next working day would be the following morning.

To assist, there is a field in Dim_Date for [IsHolidayUK] and [IsWeekday] as (true/false), and [StartDate] in the extract which defaults to 09:00 if outside normal working hours so i'm guessing that i need a subquery to filter out those holiday and weekend dates somehow?

Any advise or help would be greatly appreciated, and thanks in advance!

[javirmerino · Accepted solution]

As i was typing, i think i resolved it;

NextWorkingDay = 
minx( 
    FILTER(
        ALL(Dim_Date), Dim_Date[IsWeekday] = TRUE  && 
        Dim_Date[IsHolidayUK] = FALSE && 
        Dim_Date[Date] > SELECTEDVALUE('SysAid Extract'[ReceivedDate]) 
    ),Dim_Date[Date])

This looks to be doing what i need for the moment so i may well have answered my own query.

I love it when a plan comes together...