Hi,
I would like to find the next third Wednesday given a date? Can it be done in Power BI to calculate the next third Wednesday when given a date. Example: if I am given 31 May 2020, the next third Wednesday it should give is 17 Jun 2020. If I am given 1 May 2020, it will give me 20 May 2020.
A similar question has been asked before, please review this post to see if that helps you to resolve it.
Should you need any further assistance, just provide a sample PBIX file.
Thanks!
Greetings:
What you require is the next 3rd Wednesday from a given date. To solve this you need the Tuesday week ending date of your date and by adding 15 days you will get the 3rd Wednesday going forward.
In Query Editor, in the dates table:
1st Step:
Add a column name it “3rdWed” and insert/type formula: Date.AddDays( Date.EndOfWeek( [Date], 3 ), 15)
Note1 3 above refers to Tuesday,
Note2 15 is used because the 1st Wed = 1, the 2nd Wed =8 and finally the 3rd Wed = 15
2nd Step: Assign the new colum as type: Date
3rd Step: After you exit the editor back to PBI desktop, format your column to your prefered date type.
To Trial run this I used a generic date table added 2 columns and the resulting mcode as follows:
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "W_End_Tue", each Date.EndOfWeek( [Date] , 3 )),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Date3rdWed", each Date.AddDays( Date.EndOfWeek( [Date], 3 ), 15)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Date3rdWed", type date}, {"W_End_Tue", type date}})
Then I created a table to validate and it looks OK.
Hope this takes care of the matter. Thank you and @Melissa for the opportunity to be of service.
Have a great day.
Richard D.
Thanks @Melissa and @richard_d
I am looking for Next 3rd Wed of the Month rather than the 3rd Wednesday from the date. If the Date given is 1 May 2020, then the next third Wed is 20 May 2020. If the Date given is 25 May 2020, then the next third Wed is 17 Jun 2020 (rather than 10 Jun 2020). Thanks
We can call this the month end date on the third Wednesday of the Month. This means any date in the current month greater than the 3rd Wednesday of the Month rolls over to the next Month 3rd Wednesday of the Month.
With M Code, query editor, I will add 3 custom columns in the date table.
3W: This is the 3rd Wednesday of the current month
When you are done you can remove 3W and 3W+ in the applied steps ( Query Settings)
This is the applicable M code to add in the query.
Please see below the M Code
#"Added Custom4" = Table.AddColumn(#"Reordered Columns2", "3W", each Date.AddDays(Date.EndOfWeek( Date.StartOfMonth([Date]), 4 ),14)), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "3W+", each Date.AddDays(Date.EndOfWeek( Date.AddDays(Date.EndOfMonth([Date]),1), 4 ),14)), #"Added Conditional Column" = Table.AddColumn(#"Added Custom5", "Month End 3rd Wednesday", each if [Date] > [3W] then [#"3W+"] else [3W]), #"Changed Type4" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Date", type date}, {"3W", type date}, {"3W+", type date}, {"Month End 3rd Wednesday", type date}}), #"Removed Columns3" = Table.RemoveColumns(#"Changed Type4",{"3W", "3W+"})in
#“Removed Columns3”
This shows the applicable month end date depending if date is the 3rd Wednesday of this month or rolls over to the the following month if date is greater than 3rd Wednesday of current month.
Have a great day. This step-by-step approach with M-Code will help resolve this matter. Good luck!!!
Richard D.
Thanks @richard_d for the solution 
@jonatong Here is the dynamic solution for your requirement using DAX.
You can use this logic for next any number of day.
Like next 3rd Wed or 2nd Tue or 4th Fri…
Just change the 2 variables Num and Day.
3 Wednesday =
VAR Num = 3
VAR Day = “Wed”
VAR CurrentDate = ‘Date’[Date]
VAR DayTable =
TOPN (
Num,
FILTER ( ‘Date’, ‘Date’[Date] >= CurrentDate && ‘Date’[WeekDay] = Day ),
‘Date’[Date], ASC
)
VAR NumofRows =
COUNTROWS (DayTable )
RETURN
CALCULATE ( MAX ( ‘Date’[Date] ), FILTER ( DayTable, NumofRows = Num ) )
