Identify next Wednesday & then Thursday on a Bi Weekly Rotation

[StuartSmith]

I have a date table with a date column and need to identify the 1st Wedneday (starting from 01st Oct 2023 {01/10/2023}) and then the 2nd Thursday, then the 2nd wednesday, 2nd Thursday and so on.  Similar to the table below...

Date		Day of Week Name		Wednesday/Thursday
01/10/2023		Sunday		0
02/10/2023		Monday		0
03/10/2023		Tuesday		0
04/10/2023		Wednesday		1
...		...		...
17/10/2023		Tuesday		0
18/10/2023		Wednesday		0
19/10/2023		Thursday		1
...

04/10/2023 Wednesday
19/10/2023 Thursday
01/11/2023 Wedneday
16/11/2023 Thursday
29/11/2023 Wednesday
14/12/2023 Thursday
10/01/2024 Wednesday
25/01/2024 Thursday
07/02/2024 Wednesday
22/02/2024 Thursday
...

How can I create a culculated column that will do the above.

Thanks in advance.

[djurecicK2 · Accepted solution]

Hi @StuartSmith,

 Take a look at this thread:

https://community.fabric.microsoft.com/t5/Desktop/Return-the-2nd-Tuesday-of-every-Month/m-p/317728

Also here is a .pbix example:

https://1drv.ms/u/s!ApyQEauTSLtO6nOEn8lsK7vCA9Dd?e=0Xfg0p

[Anonymous · Accepted solution]

Hi @StuartSmith ,

Based on your description, I created simple samples and you can check the results below:

DimDate = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "DayName", FORMAT ( [Date], "DDDD" ),
    "WeekDay", WEEKDAY ( [Date],2 ),
    "WeekStart",
        [Date] - WEEKDAY ( [Date], 2 ) + 1
)

For Wed or Thr = 
var _t=ADDCOLUMNS('DimDate',"S",RANKX(FILTER(ALL('DimDate'),[WeekDay]=EARLIER([WeekDay])),[Date],,ASC,Dense))
return MAXX(_t,[S])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[StuartSmith]

Thanks, will review shortly and get back to you.

[djurecicK2 · Accepted solution]

Hi @StuartSmith,

 Take a look at this thread:

https://community.fabric.microsoft.com/t5/Desktop/Return-the-2nd-Tuesday-of-every-Month/m-p/317728

Also here is a .pbix example:

https://1drv.ms/u/s!ApyQEauTSLtO6nOEn8lsK7vCA9Dd?e=0Xfg0p

[StuartSmith]

Thanks, I aleady have the 2nd Tuesday code in my report, but couldnt figure out the wednesday/thursday bit.