Add column showing number of workday for the month based on a date value

[inc111]

Hi guys,

I wanted to add a column to populate the number of work days based on an existing date column, do you know how I get this working?E.g. for 01/14/2016, the month is Jan/2016 and there are 23 work days.

Thanks in advance,

INC111

[v-yulgu-msft · Accepted solution]

Hi @inc111,

First, you need a calendar table. In below example, I specifcy Monthday as the first day of each week.

dim date =
FILTER (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2016, 12, 31 ) ),
        "WeekDay", WEEKDAY ( [Date], 2 )
    ),
    [WeekDay] <= 5
)

Then, add a calculated column in your fact table.

number of workday =
CALCULATE (
    COUNT ( 'dim date'[Date] ),
    FILTER (
        ALL ( 'dim date' ),
        'dim date'[Date].[Year] = YEAR ( EARLIER ( Table3[BillingDate] ) )
            && 'dim date'[Date].[MonthNo] = MONTH ( EARLIER ( Table3[BillingDate] ) )
    )
)

Best regards,

Yuliana Gu

[Anonymous]

Hey @v-yulgu-msft ,

Is it possible to get this to filter down to holidays? My compnay observes new years day so it would be 22 days in January. I'd also need to filter by all company holiday. Any help is appreciated. 

[inc111]

@v-yulgu-msft Thank you!

[sokg]

In Power Query editor

1. Convert billingdate to date type

2. Add column

then day --> name of date

3. Add column as 2 but day --> day of week

4. Add a conditional column like if day of week is 5 or 6 (depence of locale settings) then "weekend" else "working day".

Is that you want???