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: https://drive.google.com/file/d/1MJvWYuJGLBbg4BaCAAUo-iySeNWRj3U0/view?usp=sharing
2.) Luxemburg holidays: https://drive.google.com/file/d/1ZwVvo95bnkZ1pHQpcRT4YFPg7DAye1Wi/view?usp=sharing
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:
// fnNETWORKDAYS
let func =
(StartDate as date, EndDate as date, optional Holidays as list, optional StartOfWeek as number) =>
let
// 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
in
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 = " www.TheBIccountant.com https://wp.me/p6lgsG-2fA . ",
Documentation.Version = " 2.1 Catering for negative duration",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ",
Code = " ",
Result = " "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
And this is how the mask for entries look like when this script is invoked:
https://drive.google.com/file/d/1BREL19XtUI859a6nQCGvueUL4D9S0UKw/view?usp=sharing
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!
Sincerely
Eugene
