Dax function to calculate 7th working day of next month

[Jyotisachin]

In power bi i have month column and i need to get 7th working day of the month in month column that excludes Saturday and Sunday

[Anonymous]

Hi @Jyotisachin 

Thank you for your prompt reply @ValtteriN !

Please try the DAX @ValtteriN provides, after my testing, it can be perfectly adapted to your needs.

Best Regards,

Jayleny

[Jyotisachin]

Hi , Thank you, I need 7th working day of next month. For example I have column of month and if month is Jan'24, then I need 7th working day of Feb'24 (excluding weekends and if any holiday list i have). Itried below but still it is not giving exact 7th working day for all the months. I think I am missing something,

[Hazenm · Accepted solution]

I assume this is a calendar table with rows on the day? And you want to specifically mark each 7th day of the working month? 
You could do something like this: 
Create 1 calculated column that is a Year-Month column. If you're in fiscal periods, you can even make this a number format such as "202401" which is 'Calendar Table'[Fiscal Year] & FORMAT('Calendar Table'[Period],"00")
Or if it's just a regular calendar table, just do something like: 
Year Month = 
YEAR('Calendar Table'[Day]) & " - " & MONTH('Calendar Table'[Day)

So now that you have the 'year - month' column create one or two more columns, depending on how you want to do it. You could create a column that marks whether each row is a weekend and then count that. Doing something like = 
Working Day = 
VAR weekDay = WEEKDAY('Calendar Table'[Day])
VAR weekend = weekDay = 1 || weekDay = 6
RETURN
NOT weekend

You might also want to work off a holiday calendar and you can add additional logic for holidays. 
But the other way which might be simpler to return your one issue is just: 

7th Working Day = 
VAR curYearMonth = 'Calendar Table'[Year Month]
VAR startMonth = 
MINX(
    FILTER(
       'Calendar Table'[Year Month] = curYearMonth
    ),
    'Calendar Table'[Day]
)
VAR curDay = 'Calendar Table'[Day]
VAR curWorkDays = NETWORKDAYS(startMonth,curDay)
RETURN
curWorkDays = 7

[ValtteriN]

Hi,

Here is one way to do this:

7th workday =

var _year = [Year]

var _month = MONTH([Date])

RETURN

CALCULATE(MAX([Date]),

FILTER('Calendar',

NETWORKDAYS(STARTOFMONTH('Calendar'[Date]),[Date])=7

&&'Calendar'[Year]=_year && MONTH('Calendar'[Date])=_month

))

End result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/