Working Days for any given month/period

[Anonymous]

Hi Experts.

How could i work out the number of working days for any given month for any of my four countries - so if i select October on Slicer it gives me the correct number of WD for October for

1. Ireland

2. Tawain

3. India

4. USA in a central matrix table.....

The attached images show calander dat i have scrb from a website...

[PattemManohar]

@Anonymous Please try this... I've used the "Date" dimension table in-addition to the "Holidays" table

WorkingDays = 

VAR CWD = CALCULATE(COUNT(DateTable[DateKey]),FILTER(DateTable,DateTable[DayOfWeekNo] <> 1 && DateTable[DayOfWeekNo] <> 7 && VALUE(DateTable[MonthNo]) = MONTH(EARLIER(Holidays[Date]))))
VAR Hol = CALCULATE(COUNT(Holidays[Date]),FILTER(ALL(Holidays),MONTH(Holidays[Date])=MONTH(EARLIER(Holidays[Date])) && Holidays[Country] = EARLIER(Holidays[Country]) && Holidays[WeekDay] <> "Saturday" && Holidays[WeekDay] <> "Sunday"))

RETURN CWD - Hol

[Anonymous]

Hi pattenmanhar 

Thanks for the feedback do you have your sample pbix file....i have just tried your formula and not getting the same end result.

[v-shex-msft]

HI @Anonymous,

Can you please share some sample data for test and coding formula?

BTW, you can also try to use belwo formula if it works for your requirment:

working days except weekend and holiday =
VAR endDate =
    IF ( Table[End] <> BLANK (), Table[End], TODAY () )
VAR _calendar =
    FILTER ( CALENDAR ( Table[Start], endDate ), WEEKDAY ( [Date], 2 ) <= 5 ) //calendar wihtout weekend
VAR _holidayList =
    CALCULATE (
        VALUES ( Holiday[Date] ),
        FILTER ( ALLSELECTED ( Holiday ), Holiday[AREA] IN VALUES ( Table[AREA] ) )
    ) //holiday date list
RETURN
    IF (
        COUNTROWS ( _holidayList ) > 0,
        COUNTROWS ( EXCEPT ( _calendar, _holidayList ) ),
        COUNTROWS ( _calendar )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Sheng. i have a measure in column Workingdays table Taiwan Calender...which is not correct,

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to use below measure to calculate working days:

Measure =
VAR workdaylist =
    CALCULATETABLE (
        VALUES ( DimDate[Date] ),
        WEEKDAY ( DimDate[Date], 1 ) IN GENERATESERIES ( 2, 6, 1 )
    )
VAR holiday =
    CALCULATETABLE (
        VALUES ( 'Taiwan Calendar'[Date] ),
        ALLSELECTED ( 'Taiwan Calendar' ),
        VALUES ( 'Taiwan Calendar'[Country] )
    )
RETURN
    COUNTROWS ( EXCEPT ( workdaylist, holiday ) )

>> i have a measure in column Workingdays table Taiwan Calender...which is not correct,

AFAIK, current power bi not support to create dynamic calculated column/table based on slicer/filter.

BTW, I haven't found any country filters in your sample, how did your filter on it?

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Sheng

Thats the next stage....this is sam.le data i sent across and also many thanks for the excellent feedback.

[Anonymous]

Sheng see link. File in drop box.

The file has two tables a dim date and Taiwan holidays calendar I want to work out the measure as per your post.https://www.dropbox.com/s/sy8h2njxsnhzwvb/Testfile-HMDRMvaVg03lviJAYLhElsM9CHCD9pHYE0QAQPOPrNSacGy2j...