Find date of 3rd Thursday of the month

DAX DAX Calculations
1

Hello,

I’d like to write a measure or add a calculated column that identifies the 3rd Thursday of each calendar month. I feel like this should be easy, but I haven’t been able to figure it out. :slightly_frowning_face:

Help?

Thanks,
Nic

2

@Nic,

Here’s a thread that shows exactly how to do this, both as a measure and calculated column:

  • Brian
3

If you translate the DAX solution Brian posted to (Power Query) M, you’ll get something like this:

Table.AddColumn(#"Added Custom", "3rd Thursday", each Date.Day([Date])>21 and Date.Day([Date])<=28 and Date.DayOfWeek([Date], Day.Monday) = 3, type logical)

.
Or if you want to have the date returned instead of a boolean filter:

Table.AddColumn(#"Added Custom", "3rd Thursday", each if Date.Day([Date])>21 and Date.Day([Date])<=28 and Date.DayOfWeek([Date], Day.Monday) = 3 then [Date] else null, type date)

4

Hi Nic

For a calculated column in your date table, this method will show the 3rd Thursday of that month as a cut-off date and after the 3rd Thursday it will show the 3rd Thursday of the following month.

3rdThursday =
VAR Thurs_TM = EOMONTH( Dates[Date], -1) +1 - WEEKDAY( EOMONTH( Dates[Date], -1) - 4 ) +21
VAR Thurs_NM = EOMONTH( Dates[Date], 0) + 1 - WEEKDAY( EOMONTH( Dates[Date], 0 ) -4 ) +21
RETURN
IF( Dates[Date].[Date] > Thurs_TM, Thursday_NM, Thurs_TM)

Number 4 in the formula refers to Thursday.

In case you want the Third Thursday to apply to the whole month, then use this expression for your calculated column.

3rdThursday = EOMONTH( Dates[Date], -1) +1 - WEEKDAY( EOMONTH( Dates[Date], -1) - 4 ) +21

Hope this helps and have a nice day.

Richard

1 Like
5

I was able to use your solution with a couple tweaks to get what I needed. Thanks so much for taking the time!

6

Hi, Melissa.

Richard’s solution with a couple changes worked best for me, but thank you so much for taking the time to answer! Your M code helped me clarify what I needed to do. Much appreciated.

7

Thanks, Brian. Richard’s solution worked best for this use case, but I do appreciate you taking the time.

1 Like
8

Add a new column in Power Query with below formula

= Date.StartOfWeek(#date(Date.Year([Date]),Date.Month([Date]),21),Day.Thursday)

Above will take 21st day of the month
and will consider Thursday as the first day

Result will show the 3rd Thurday of the month, only catch is that you should have a continuous date.