- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 10:09 AM
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.
Solved! Go to Solution.
Accepted Solutions
Re: Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 05:06 PM
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])
All Replies
Re: Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 10:56 AM
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
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
Re: Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 11:12 AM
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.
Re: Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 11:25 AM
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)
Re: Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 12:29 PM
That appears to work returning true or false, however I want to return the actual date of the 2nd Tuesday.
Re: Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 12:54 PM
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())
Re: Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 01:30 PM
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.
Re: Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 02:35 PM
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.
Re: Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 02:58 PM
So do you mean every row for the month will carry the date that happens to be the 2nd tuesday of each month?
Re: Return the 2nd Tuesday of every Month
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2017 05:06 PM
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])