Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Recreates the NETWORKDAYS function from Excel with some bonus measures and one that needs fixed or demonstrates a bug/documentation glitch.
NetWorkDays calculates the number of days between two dates sans weekends.
NetWorkDays = VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date])) VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2)) RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
DaysHoursMinutes provides a nice display format for the number of days, hours and minutes between two dates
DaysHoursMinutes = DATEDIFF(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]),DAY)
& " Days " & HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1))
& " Hours " & MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Minutes"
NetWorkDaysHoursMinutes displays net work days in a nice display format
NetWorkDaysHoursMinutes =
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date]) & " Days " &
HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Hours "
& MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Minutes"
Finally, this one tries incorporates holidays. While the documentation indicates that you can use DATE, you cannot. But the following code does work to remove December 25th as a work day.
NetWorkDaysHolidays =
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
//VAR Holidays = DATATABLE("Date",DATETIME,{{}})
VAR Holidays1 = DATATABLE("Date",DATETIME,
{
{"12/25/2017 12:00:00 AM"}
})
VAR Calendar2 = EXCEPT(Calendar1,Holidays1)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date])
Finally, finally, this one is Holidays with days, hours and mintues:
NetWorkDaysHolidaysDaysHoursMinutes = VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date])) //VAR Holidays = DATATABLE("Date",DATETIME,{{}}) VAR Holidays1 = DATATABLE("Date",DATETIME, { {"12/25/2017 12:00:00 AM"} }) VAR Calendar2 = EXCEPT(Calendar1,Holidays1) VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2)) RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date]) & " Days " &
HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Hours "
& MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Minutes"
eyJrIjoiYjFhNWIwMmMtMTlhOC00YjNkLTlmNmMtMGYxMjcwOWQzZWRkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
@kman42, Ahhhh, the boundary cases. Glad you did re-open the thread. Try this variation out, should account for the boundary case that you specify and frankly cleans up the code a bit.
NetWorkDaysHoursMinutes =
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
VAR Days = COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
VAR Hours = HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1))
VAR Minutes = MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1))
VAR NetWorkDaysHoursMinutes =
SWITCH(TRUE(),
Days = 1 && HOUR(MAX(NetWorkDays[review date])) < HOUR(MAX(NetWorkDays[created date])),
"0 Days " & Hours & " Hours " & Minutes & " Minutes",
Days & " Days " & Hours & " Hours " & Minutes & " Minutes"
)
RETURN NetWorkDaysHoursMinutes
Hi Greg, i am using a direct query and it throws up an error saying countx is not allowed as part of calculated columns dax expressions on Direct Query Mode.. Please Help!!!!
Are you sure you are using the formula in a measure and not in a Column? According to this article, COUNTX should work in a measure:
Hi Greg,
I am definatly using a measure!
Below is the Error Message:
MdxScript(Model) (189, 17) Calculation error in measure 'fact_CRM_Incident'[Business Days Active]: The start date in Calendar function can not be later than the end date.
I would like to also reference my holiday flag column which is a Yes/No field in my dim_date table.
On another note the Closed on date column contains blanks if a ticket is in an "active" or Open state in which case the business days calc should continue to count till current day until a closed date is added in the dataset.
Appreciate the help mate.
I am receiving the same error. Did you ever figure out the solution?
Thanks!
I infact did i used Greg's solution and created another measure to calculate weekends and holidays and subtracted from the above solution.
@Anonymous wrote:
I am receiving the same error. Did you ever figure out the solution?
Thanks!
What if I have some empty fields?
In my query the "review date" for ongoing item is blank. and this measure provide gives me an error.
I don't wan't to filther them out. I still want to show all row in my dashboard.
Thank you in advance.
Regards
Very nice scripts, but what if I want to aggregate the network days by another column from the NetWorkDays table?
Eg. if I add a "Name" column to the NetWorkDays table and add John and Jane Doe and one more row:
When using the NetWorkDays measure it works on a row by row level, but if I try to aggregate by the 'Name' column the measure seems to be incorrect:
Any idea why and how to fix it?
I have included the modified PBIX file.
Thanks for your help,
NetWorkDays =
VAR Calendar1 = CALENDAR(MAX(KontElemente[Datum der Besichtigung]);MAX(today())
I didn´d understand the part.
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
Can u help me out?
If you are referring to the VAR Calendar2 part, what that does is adds a column to the temp calendar table using the WEEKDAY function with the parameter 2, which essentially codes things M=1, T=2, W=3, Th=4, F=5, Sa=6, Su=7. So, the next step, the RETRUN filters out anything 6 or 7 to exclude Saturdays and Sundays.
It´s not working. Guess I´m the problem and not the formula
You are missing a ) at the end of your line beginning with VAR Calendar1
Hi @Greg_Deckler, I used the function dor the networkdays but I get this error. Is it possible to have this but instead of a closed date column to use the NOW/TODAY date?
Thanks for the help.