Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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,
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
Hi,
Here is one way to do this:
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!
Proud to be a Super User!
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |