Service Duration in seconds Calculation in DAX
Hi, I have this code in excel that calculates the service duration (in seconds) of a Support ticket, excluding bank holidays, weekends and out of office hours. Excel Code - =((NETWORKDAYS.INTL(A2,B2,1,$H$2:$H$11)-1)*("18:00"-"7:00")+IF(NETWORKDAYS.INTL(B2,B2,1,$H$2:$H$11),MEDIAN(MOD(B2,1),"7:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,1,$H$2:$H$11)*MOD(A2,1),"7:00","18:00"))*86400 My Dax code so far - Measure.ServiceHours = VAR _StartDate = SELECTEDVALUE(TICKET_MASTER[TICKETSUBMITDATE]) VAR _EndDate = SELECTEDVALUE(TICKET_MASTER[CLOSEDTIME]) RETURN ((NETWORKDAYS(_StartDate, _EndDate,1,BankHolidayDates)-1)*("18:00"-"7:00")+IF(NETWORKDAYS(_EndDate,_EndDate,1,BankHolidayDates),MEDIAN(mod(_EndDate,1),"7:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS(_StartDate,_StartDate,1,BankHolidayDates)*MOD(_StartDate,1),"7:00","18:00"))*86400 Unfortunately Median dax code works differently to excel, can anyone help me convert this into DAX? Example of excel code working below
NETWORKDAYS with filtered column of dates
Hello Wonderful Community and a Happy Monday to you all, Hopefully a fairly quick and simple question for you here. I'm pretty excited about NETWORKDAYS hitting Power BI and so was hoping to use it in combination with a UK .gov API of Holidays. I've succesfully pulled in the table from the API and placed a few filters on it to get just this year's Bank Holidays, and thought I'd test NETWORKDAYS in the run-up to Christmas (on the hottest day ever!). As per https://hello-safe.co.uk/business-insurance/tools/working-days-calculator, I'm expecting the result of 114 for England and Wales and 113 for Scotland. As per the documentation, I'm using the following for my DAX column; daysToChristmas = VAR _BankHolidays = {'bank-holidays'[events.date]} RETURN NETWORKDAYS(TODAY(), DATE(2022,12,25), 1, _BankHolidays) But this is my results (noting Scotland is one day out and the tables are correct). It's as if it's only subtracting one holiday. Appreciate this can be done via other means, but really want to crack this with this DAX function. Any help, greatly appreciated! Edit: API endpoint is here, allows anonymous access: https://www.gov.uk/bank-holidays.json
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 EugeneNegative Net Work Days
Good afternoon! I'm trying to calculate net work days for my company. I was using a DAX syntax to calculate it, however we do accept NEGATIVE networkdays at our office and the DAX is rejecting the negative numbers. Does anyone have a solution to calculate a networkdays difference that will allow me to have negative? This was the dax formula I was using ""NetWorkDays = (Var TBL_Date=CALENDAR('COMPLETED (2)'[BATCH DATE],'COMPLETED (2)'[COMPLETED DATE]) vAR TBL_FinalDate= ADDCOLUMNS(TBL_date, "WorkingDay",if(weekday([Date],2)>=6,0,1), "Holiday",iferror(lookupvalue('MY COMPANY CALENDAR'[Holiday Count],'MY COMPANY CALENDAR'[Holiday Date],[Date]),0) ) Return sumx(tbl_finalDate, if([WorkingDay]=1 && [Holiday]=0, 1, 0))"" The error I got was " The start date in Calendar function can not be later than the end date"
