Determine the number of business / workdays in the current month & previous month

[Anonymous]

I am trying to determine the number of business / workdays in the current month & the previous month.

I have a "Production" Fact Table on my dashboard with a period of 2 years.

I would also like to be able to do the following :

1) Determine the number of business / workdays in the current month & the previous month.

2) Allow the number of business / workdays in the current month & the previous month to change according to the selected "Production" date. (not compulsary)

3) Incorporate manual input of holidays possibly in sharepoint (yet to decide how) into the calculation of number of business / workdays in the current month & the previous month based on selected date.

What i have done so far :

1. Create a table :

Workday Calendar = CALENDAR(EOMONTH(TODAY(),-2)+1,EOMONTH(TODAY(),-2+2))
**note that this table do not have every single date in current month. (that's why i created a custom table)

2. Add Column to determine Day of the week :

Day = FORMAT('Workday Calendar'[Date],"dddd")
3. Add measure

Current Month Workdays = CALCULATE(COUNT('Workday Calendar'[Date]),'Workday Calendar'[Day] <> OR("Saturday","Sunday"),MONTH('Workday Calendar'[Date])=MONTH(TODAY()))

Why this did NOT work :

1) No relationship / connection with current model.

Final measures will be able to determine :
(1) to multiply "average daily current month estimate" * "number of current month working days" = current month production estimate

(2) "number of previous month working days"

[amitchandak]

@Anonymous , New column in date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)

as column

Work day of month = Sumx(filter(Date, [Month year] = earlier([Month year])),[Work Day])

Work day of previous month = Sumx(filter(Date, eomonth([Date],0) = eomonth(earlier([Date]),-1)  ),[Work Day])

Measure

MTD WorkDay= CALCULATE(SUM('Date'[Work Day]),DATESMTD('Date'[Date]))
last MTD WorkDay= CALCULATE(SUM('Date'[Work Day]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

[littlemojopuppy · Accepted solution]

H @Anonymous 

With a date table that looks something like this (and make sure it is marked as a date table!)

Create these measures

Workdays = 
    COUNTROWS(
        FILTER(
            'Calendar',
            AND(
                'Calendar'[Weekday] <> 1,
                'Calendar'[Weekday] <> 7
            )
        )
    )

Workdays PM = 
    CALCULATE(
        [Workdays],
        PREVIOUSMONTH('Calendar'[Date])
    )