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:

DateIs Workday
2024-01-010
2024-01-021
2024-01-031
2024-01-041
......

 

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!

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)

3 Replies

  • 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)