Mark second workday in calendar table

[H3nning]

Hi, Im struggling with a little challenge and hope some is able to solve it!

I have a calendar table with two columns:

Date	Is Workday
2024-01-01	0
2024-01-02	1
2024-01-03	1
2024-01-04	1
...	...

I need to mark the second workday depending on the Is Workday column (not depending on day of week). So in case of January 2024 this would be the 3rd of January, because 1st is marked as non workday. I need to do this for the entire table for every month each second workday. It can be a 0/1 coding for 1 being the second workday.

Thanks in advance!

[Kedar_Pande · Accepted solution]

@H3nning 

Calculated Column

SecondWorkday = 
VAR CurrentMonth = MONTH('Calendar'[Date])
VAR CurrentYear = YEAR('Calendar'[Date])
VAR WorkdayRank = 
RANKX(
FILTER(
'Calendar',
'Calendar'[Is Workday] = 1 &&
MONTH('Calendar'[Date]) = CurrentMonth &&
YEAR('Calendar'[Date]) = CurrentYear
),
'Calendar'[Date],
,
ASC
)
RETURN
IF(WorkdayRank = 2, 1, 0)

This approach dynamically calculates the second workday for every month in your table.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Kedar_Pande · Accepted solution]

@H3nning 

Calculated Column

SecondWorkday = 
VAR CurrentMonth = MONTH('Calendar'[Date])
VAR CurrentYear = YEAR('Calendar'[Date])
VAR WorkdayRank = 
RANKX(
FILTER(
'Calendar',
'Calendar'[Is Workday] = 1 &&
MONTH('Calendar'[Date]) = CurrentMonth &&
YEAR('Calendar'[Date]) = CurrentYear
),
'Calendar'[Date],
,
ASC
)
RETURN
IF(WorkdayRank = 2, 1, 0)

This approach dynamically calculates the second workday for every month in your table.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[H3nning · Accepted solution]

It works if I add a filter for Is Workday:

SecondWorkday = 
VAR CurrentMonth = MONTH('Calendar'[Date])
VAR CurrentYear = YEAR('Calendar'[Date])
VAR WorkdayRank = 
RANKX(
FILTER(
'Calendar',
'Calendar'[Is Workday] = 1 &&
MONTH('Calendar'[Date]) = CurrentMonth &&
YEAR('Calendar'[Date]) = CurrentYear
),
'Calendar'[Date],
,
ASC
)
RETURN
IF(WorkdayRank = 2 && 'Calendar'[Is Workday] = 1, 1, 0)

[H3nning]

Hi thanks for your reply. It does not produce the right result though.

In March 2024 it results in:

Date	Is Workday	Result
2024-03-01	1	0
2024-03-02	0	1
2024-03-03	0	1
2024-03-04	1	1
...