Forum Discussion

H3nning's avatar
H3nning
Icon for Helper V rankHelper V
1 year ago
Solved

Mark second workday in calendar table

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 2...
Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    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's avatar
    H3nning
    1 year ago

    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)
Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
1 year ago

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's avatar
    H3nning
    Icon for Helper V rankHelper V
    1 year ago

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

    In March 2024 it results in:

    DateIs WorkdayResult
    2024-03-0110
    2024-03-0201
    2024-03-0301
    2024-03-0411
    ...  
  • H3nning's avatar
    H3nning
    Icon for Helper V rankHelper V
    1 year ago

    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)