itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: weekend/holiday

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:

  • 2 means that my week starts on Monday so 6 is Saturday and 7 is Sunday
  • For more options, check this Microsoft WEEKDAY page

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:

  • If the date column has only the date (25/03/2024), by default the time is 00:00:00 (25/03/2024 00:00:00)
  • If the date column includes the time (25/03/2024 17:23:43), to reset it at 00:00:00 (25/03/2024 00:00:00), use the DATEVALUE function: DATEVALUE('table'[start_date])

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”

  • 'table'[end_date] - 'table'[start_date] = 1 day

Now, I want to count numbers of day between “25/03/2024 17:23:43” and “26/03/2024 10:00:17”

  • 'table'[end_date] - 'table'[start_date] = 0 day
  • 'table'[end_date] - DATEVALUE('table'[start_date]) = 1 day

NOTE:

  • To get 1 day without using DATEVALUE, the time for 26 should be the same as for 25 or later
  • Don’t forget to format it into “whole number” in the “data type” field

Interesting Topics