Calculating number of business day excluding Country Holidays
Hi, I am trying to figure out how to calculate the number of business days (Age of Order) between the date an order was submitted and today (using a table with list of Holidays per country) Here are my tables: COUNTRY TABLE HOLIDAYS TABLE ORDER TABLE I am able to add a column by invoking the following custom function – this will exclude week-ends and also any dates in the Holidays table. = (StartDate as date, Holidays as list) => let EndDate = DateTime.Date(DateTime.LocalNow()), // 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(_, 1) < 5 ), // Count the number of days (items in the list) CountDays = (List.Count(DeleteWeekends) * Sign) - 1 in CountDays The piece that I can’t seem to figure out is how to only apply holidays matching the country of the order. Thanks for you help
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: 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 Eugenedifference in average revenue between different periods of the same holiday
I don't know how to calculate the 2 columns in red. Since it is not possible to use date dimensions, which dax structure should I use to calculate the percentage difference between these values for the same holiday period that occur at different times of the year? Holida Average Revenue Last Average Revenue Diff % 2018 100 30/03 150 31/03 50 2019 200 100 50% 10/04 300 150 11/04 100 50 2020 150 200 -33% 27/04 200 300 28/04 100 100 Help me plz. THX!
