Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

Calculating net working days considering public holidays of two Countries

Dear forum members,

I am reaching out to you as I have not enough experience with DAX by myself or anyone else I can turn to.
Here is what I'm trying to achieve:

I want to be able to calculate the net working days for a project.

It's important to consider weekends and public holidays. The crux here is that the script has to get its public holidays data from two different countries. In this case Germany and Luxemburg. further it has to check in a specific table "projects" and column "Client" if the data contains the word "LUX" that indicates, that the client is based in Luxemburg. Otherwise, the client is based in Germany.
So, based on the location of the client for whom the project has been created, the correct table with all the public holidays needs to be selected dynamically.


This is how both tables look like which were imported into Power BI as lookup tables for the public holidays (text is in German):

1.) German holidays:

2.) Luxemburg holidays:


I have found a DAX script on a website which basically does what I want but it can only look into one table with public holidays and not into two or more and it doesn't check if a specific word occurs in a specific column.

This is the DAX script I found:

let func =  
(StartDate as date, EndDate as date, optional Holidays as list, optional StartOfWeek as number) =>
    // optional StartOfWeek, if empty the week will start on Monaday
    startOfWeek = if StartOfWeek = null then 1 else StartOfWeek,
    // Providing for logic where EndDate is after StartDate
    Start = List.Min({StartDate, EndDate}),
    End = List.Max({StartDate, EndDate}),
    // Switch sign if EndDate is before StartDate
    Sign = if EndDate < StartDate then -1 else 1,
    // Get list of dates between Start- and EndDate
    ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)),
    // if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays;
    // otherwise continue with previous table
    DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )),
    // Select only the first 5 days of the week
    // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week
    DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, startOfWeek) < 5 ),
    // Count the number of days (items in the list)
    CountDays = List.Count(DeleteWeekends) * Sign
    CountDays ,
documentation = [
Documentation.Name =  " Date.Networkdays.pq ",
Documentation.Description = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in holidays. ",
Documentation.LongDescription = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in (optional) holidays. ",
Documentation.Category = " Date ",
Documentation.Source = " .   ",
Documentation.Version = " 2.1 Catering for negative duration",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description =  "  ",
Code = "  ",
Result = "  "]}]
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))


And this is how the mask for entries look like when this script is invoked:

How can I add a second select for another holidays table and let the script decide wheter it should look into the holidays list of Germany or Luxemburg for a predefined search word like "LUX" before it decides which of both or n public holidays tables is the right one?

Thank you very much!



Not applicable

The script is not in DAX. It's in M - the language of Power Query.

There you go... now you know how increadibly advance my M skills are 🤣.

I didn't even know that this was M.

Anyway, thanks for the hint!

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors