cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashik1992
Helper I
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
Mohammad_Refaei
Solution Specialist
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 ()
    )
)

 

Please check the sample file.

View solution in original post

3 REPLIES 3
ashik1992
Helper I
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."

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
Solution Specialist
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 ()
    )
)

 

Please check the sample file.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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 Kudoed Authors