In Power BI, to exclude the weekend between 2 dates, I will use the NETWORKDAYS function, this function allows to count only working days, the formula will be like that:
NETWORKDAYS('table'[start_date],'table'[end_date])
This formula will exclude by default Saturday and Sunday (weekend). If the weekend is Sunday and Monday, in this situation, I will add a parameter like this:
NETWORKDAYS('table'[start_date],'table'[end_date],2)
Additionally, if I have another table with holidays, I can exclude them in my calculation:
NETWORKDAYS('table1'[start_date],'table1'[end_date],1,VALUES('table2'[holidays_date]))
For more weekend options and holiday explanation, just refer to this Microsoft NETWORKDAYS page.
What if I want to count how many weekends between 2 dates ? In fact, the real question will be, what if I want to count the number of Saturday and Sunday between 2 dates ? I will use this formula:
COUNTROWS(FILTER(CALENDAR('table'[start_date],'table'[end_date]),WEEKDAY([Date],2) in {6,7}))
NOTE:
And lastly, I want to include holidays:
NETWORKDAYS('table1'[start_date],'table1'[end_date]) - NETWORKDAYS('table1'[start_date],'table1'[end_date],1,VALUES('table2'[holidays_date])) + COUNTROWS(FILTER(CALENDAR('table1'[start_date],'table1'[end_date]),WEEKDAY([Date],2) in {6,7}))
NOTE: to exclude the weekend, remove everything from the sign “+”
Before to close this topic, I just want to add something that is “indirectly” related:
This small difference is important based on what I need/want, I will give you 2 simple examples. The first one, I want to count numbers of day between “25/03/2024” and “26/03/2024”
Now, I want to count numbers of day between “25/03/2024 17:23:43” and “26/03/2024 10:00:17”
NOTE:
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...