Desktop

[tacobannas]

I am trying to return the 2nd Tuesday of every month. My table has a date for everyday. How can I return this based on the that field. 

[Phil_Seamark · Accepted solution]

Please try this as a caluculated column

Is 2nd Tuesday = 
VAR a = 'Dates'[Date]
VAR y = FILTER(
        ALL(Dates[Date]),
        YEAR('Dates'[Date]) = YEAR(a) && 
        MONTH('Dates'[Date]) = MONTH(a) && 
        DAY([Date]) > 7 && 
        DAY([Date]) < 15 && 
        WEEKDAY([Date],3) = 1)
RETURN MAXX(y,[Date])

[MFelix]

Hi @tacobannas,

Create these two column:

MonthWeekday = MONTH('Calendar'[Date]) & WEEKDAY('Calendar'[Date]) 

weekday_Number = WEEKDAY('Calendar'[Date])

Then create this measure:

2nd Tuesday =
VAR Date_Max =
    MAX ( 'Calendar'[Date] )
VAR Month_week_Max =
    MAX ( 'Calendar'[MonthWeekday] )
RETURN
    IF (
        CALCULATE (
            COUNT ( 'Calendar'[MonthWeekday] ),
            'Calendar'[Date] <= Date_Max,
            'Calendar'[MonthWeekday] = Month_week_Max
        )
            = 2
            && MAX ( 'Calendar'[weekday_Number] ) = 3,
        "2nd",
        ""
    )

Add the measure as a filter on your visuals and you should get the result you need:

Regards,

MFelix

[tacobannas]

Thanks, that works as a measure, but I was hoping to return the 2nd Tuesday each month as a date in a column. So for everday of the month in my calendar table I would have a corresponding column with the 2nd Tuesday date. 

[Phil_Seamark]

Please try this as a calculated column

Is 2nd Tuesday = 
VAR x =  IF(WEEKDAY([Date],1) = 3 , DAY([Date]) , 0)
RETURN IF(x > 7 && x < 15 , 1 , 0)

[tacobannas]

That appears to work returning true or false, however I want to return the actual date of the 2nd Tuesday. 

[Phil_Seamark]

Hi

Please try this

Is 2nd Tuesday = 
VAR x =  IF(WEEKDAY([Date],1) = 3 , DAY([Date]) , 0)
RETURN IF(x > 7 && x < 15 , [Date] , blank())

[MarcelBeug]

A power Query solution: add a column with formula:

= Date.StartOfWeek(#date(Date.Year([Date]),Date.Month([Date]),14),Day.Tuesday)

This wil take the 14th of the year/month of the date, and from that date: the start of the week, with Tuesday as the first day of the week. So that must be the 2nd Tuesday in the month.

[tacobannas]

This appears to be working, but I need that to be filled into every row value pair, not blank in the instance it is not the 2nd Tuesday. 

[Phil_Seamark]

So do you mean every row for the month will carry the date that happens to be the 2nd tuesday of each month?

[Phil_Seamark · Accepted solution]

Please try this as a caluculated column

Is 2nd Tuesday = 
VAR a = 'Dates'[Date]
VAR y = FILTER(
        ALL(Dates[Date]),
        YEAR('Dates'[Date]) = YEAR(a) && 
        MONTH('Dates'[Date]) = MONTH(a) && 
        DAY([Date]) > 7 && 
        DAY([Date]) < 15 && 
        WEEKDAY([Date],3) = 1)
RETURN MAXX(y,[Date])