Number of working days

[nhol]

Hi,

I'm looking after counting working days function. However, not for historical months (like February in the example attached) but also for current month where my latest and greatest date in the system is up until today only (March 4th).

In other words I'm searching for a formula that will extract the month we are in based on MMMM and YYYY from my [insert_time] table and will tell me how many working days are expected to be in this month.

I can not add in date table as I'm sourcing SQL database via a DirectQuery mode. So I have some limitations there.

Thanks,

NH

[Greg_Deckler]

I have a Quick Measure in the Gallery for that. It adds a Calendar table dynamically.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

[Zubair_Muhammad]

Hi @nhol

Try this MEASURE

CountWorkingDays =
VAR Temptable =
    ADDCOLUMNS (
        VALUES ( TableName[insert_time] ),
        "WeekDay", WEEKDAY ( TableName[insert_time], 2 )
    )
RETURN
    COUNTROWS ( FILTER ( Temptable, [WeekDay] <= 5 ) )

[nhol]

Hi,

This formula provide me with "1" for each row that has a stamp date. I need a formula that based on the date, assuming March 5th 2018, it will know that we are in the month of March (2018) and it will provide me with 22 working days eventhough I don't have a full month dates in teh system.

Thanks!

NH 

[Greg_Deckler · Accepted solution]

@nhol- Try this, it takes a single input date (in the example, Calendar[Date]) and returns working days in the month.

NetWorkDays = 
VAR StartDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date])),1)
VAR EndDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date]))+1,1)-1
VAR Calendar1 = CALENDAR(StartDate,EndDate)
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

[nhol]

Brilliant!!!

Thank you so much!

Nir

[Zubair_Muhammad]

@nhol

Please See the attached pbix file here as well