networking days excluding holiday and weekend

[ashik1992]

Hi

eed a help to create networking days in each month .

I have master table in which a column called "workdate". work date starts from 01-01-2021 to 12-31-2021.

I have another table which having the holidays details. i need to find the networking days of each month considering weekends and holidays. please help me.

New Year's Day	Jan 01, 2021 (Fri)
Republic Day	Jan 26, 2021 (Tue)
Ramzan Id/Eid-ul-Fitar	May 14, 2021 (Fri)
Diwali / Deepavali	Nov 04, 2021 (Thu)
Christmas Eve	Dec 24, 2021 (Fri)

[AvinashR_19]

Here if i have city also in holidays table as you have written.. how will i consider that??

[ashik1992]

hi,

how i can calculate YTD utilization from this data?

i used this formula 

ytd utilization = CALCULATE([Invoicehour],DATESYTD('Invoice - India Region (50) xls'[workdate],"31/12")/(178*MONTH(MAX(WorkDate[Date]))))

but got this error " A function 'DATESYTD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

[Mohammad_Refaei]

DATESYTD has to be used on a proper calendar table and the YTD calculation formula is:

YTD Value =
CALCULATE ( [Measure], DATESYTD ( 'CalendarTable'[Date] ) )

So, create a proper measure then apply the same syntax on a Calendar table.

It is very important to have a separate Calendar table in your model if you are working with dates.

In Power Bi Desktop, Modeling, New Table

 Calendar = CALENDAR( DATE ( 2015,1,1 ), Date ( 2025,12,31 ) )
// You can customize the start and end date

[Mohammad_Refaei · Accepted solution]

You will have to create a relationship between the 2 tables and create a measure to count the working days:

WorkingDaysCount = 
// Assuming weekend is Sat & Sun .... adjust if required
CALCULATE (
    COUNTROWS ( 'WorkDate' ),
    FILTER (
        'WorkDate',
        NOT ( 'WorkDate'[WeekDay] IN { "Sat", "Sun" } )
            && RELATED ( Holidays[Date] ) = BLANK ()
    )
)

Please check the sample file.