topic Re: Returning 3rd and 4th Wednesday of the month in DAX Commands and Tips

Returning 3rd and 4th Wednesday of the month

jlrubio — Thu, 08 Aug 2019 20:43:09 GMT

Hello,

I am not sure whether I am posting this question in the correct area, but is there anyone who can assist me in creating a function as a filter that will give me data for the 3rd and 4th Wednesday of the month?

Re: Returning 3rd and 4th Wednesday of the month

Greg_Deckler — Thu, 08 Aug 2019 22:52:56 GMT

Probably, I just answered a question regarding filtering out alternative Thursdays. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Returning 3rd and 4th Wednesday of the month

jlrubio — Thu, 08 Aug 2019 23:03:49 GMT

Hi Greg,

Thank you so much. Is there anyway you can send me a link to the post regarding the alternative Thursdays?

Re: Returning 3rd and 4th Wednesday of the month

jlrubio — Fri, 09 Aug 2019 21:13:56 GMT

Hello,

Is there anyway you can assist me with the dax function for the 3rd and 4th Wednesday's of the month?

Thank you,

Josefina Rubio

Re: Returning 3rd and 4th Wednesday of the month

Anonymous — Fri, 09 Aug 2019 22:24:46 GMT

Can you please clarify what you mean by "creating a function as a filter that will give me data for the 3rd and 4th Wednesday of the month"?

What do you mean? I have no idea whether you're talking about DAX or Power Query... and what does it mean "give me data for the 3rd and 4th Wed"?

I'd suggest reading this: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best
Darek

Re: Returning 3rd and 4th Wednesday of the month

jlrubio — Fri, 09 Aug 2019 23:08:19 GMT

I am trying to find a way to return the third and fourth Wednesday's of the month from appointment dates ranging from June 1, 2019 thru Aug 3, 2019. There is a column in my data set that has all of the appointments and from this column, I would like to populate only the third and fourth Wednesday's of the month. Is there a way I can do this? My apologies for the confusion!

Re: Returning 3rd and 4th Wednesday of the month

Anonymous — Sat, 10 Aug 2019 13:56:51 GMT

Looks like what you need is a column in your table that will mark somehow (maybe TRUE/FALSE, or 3/4) the 3rd and 4th Wed in each month. Does the column with dates store all contiguous dates in the years you're interested in? In other words, is this a Date table? Or it's just a column with some dates?

Best
Darek

Re: Returning 3rd and 4th Wednesday of the month

jlrubio — Mon, 12 Aug 2019 16:08:52 GMT

Hello Darek,

Yes, this is exactly what I need! The column with dates are appointment dates that range from June 1, 2019 thru August 3, 2019. This is a column with dates and not a date table. Is there anyway you can help me with this if possible?

Thank you,

Josefina Rubio

Re: Returning 3rd and 4th Wednesday of the month

Anonymous — Mon, 12 Aug 2019 17:26:45 GMT

First of all, you should always, ALWAYS without exception, use Date tables that join to your date fields in other tables and you should hide the column to which you join in the tables leaving only the fields in the real Date table visible. You should (almost) never, EVER use a date column from any table apart from a proper Date table.

All time calculations must be done on the Date table. ALL. Otherwise you're very much at risk of getting into trouble.

Secondly, I'll create a Power Query script that will mark the 3th and 4th Wed in each month... and paste it in here.

Best
Darek

Re: Returning 3rd and 4th Wednesday of the month

Anonymous — Mon, 12 Aug 2019 18:03:02 GMT

Here's the file with the M code:

Example file with how to mark the 3rd and 4th Wednesday

You have to take a look at the M code in Power Query, not at the table in the report.

Best

Darek

Re: Returning 3rd and 4th Wednesday of the month

jlrubio — Tue, 13 Aug 2019 00:05:49 GMT

Hi Darek,

Thank you so much for all the help! I really appreciate the clarification, especially with looking at the M code in Power Query versus the data table. This definitely helps!

-Josefina Rubio