cancel
Showing results for
Did you mean:
Helper I

## networking days excluding holiday and weekend

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)
1 ACCEPTED SOLUTION
Solution Specialist

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 ()
)
)``````

3 REPLIES 3
Helper I

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."
Solution Specialist

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``````

Solution Specialist

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 ()
)
)``````

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors