Dax Required to return 1st Tuesday and 3rd Tuesday of a particular Month from a column(Date opened)

[Sourav_86_Babai]

Dax Required to return 1st Tuesday and 3rd Tuesday of a particular Month from a column (Date opened) which contains date in the format like 01.Jan.2020.

Also, i had Split Month Name, Day, Day Name from the above column to all separate columns but after this not able to return 1st Tuesday and 3rd Tuesday of a particular Month.

Please help.

[Greg_Deckler · Accepted solution]

You want the Date of the 1st Tuesday and 3rd Tuesday? You could do this:

First Tuesday =
MINX(FILTER('Calendar',WEEKDAY([Date],1) = 3),[Date])

[Greg_Deckler · Accepted solution]

Here is Third Tuesday:

Third Tuesday =
VAR __Table =
ADDCOLUMNS(
FILTER('Calendar',WEEKDAY([Date],1) = 3),
"Order",COUNTROWS(FILTER('Calendar',WEEKDAY([Date],1) = 3 && 'Calendar'[Date] <= EARLIER('Calendar'[Date])))
)
RETURN
MAXX(FILTER(__Table,[Order]=3),[Date])

[Sourav_86_Babai]

1st and Third tuesday shall i create as Measure or new Column?

[Greg_Deckler]

I created measures. Probably could be adapted for a column if you got rid of aggregations around certain column references.

[Sourav_86_Babai]

Third Tuesday is not working as i checked with Measures and used a slicer as visualization:

3rd Tuesday =
VAR __Table =
ADDCOLUMNS(FILTER('New FR 2020',WEEKDAY([Date opened].[Date],1) = 3),
"Order",COUNTROWS(FILTER('New FR 2020',WEEKDAY([Date opened].[Date],1) = 3 && 'New FR 2020'[Date opened].[Date] <= EARLIER('New FR 2020'[Date opened].[Date]))))
RETURN
MAXX(FILTER(__Table,[Order]=3),[Date opened].[Date])

[Greg_Deckler]

@Sourav_86_Babai  - Going to have to let me know what is going on in more detail. I based my calculations on having a date table. See attached PBIX file. Is that what you are using as well or are you using some table that has date holes in it?

[Sourav_86_Babai]

@Greg_Deckler 

Hi,

Thank you for your reply.
I guess something is missing out.

As per your file:

Date = Opened Date

Calender = New FR 2020

I tried to make changes but it is not working.

Regards.

[Greg_Deckler · Accepted solution]

You want the Date of the 1st Tuesday and 3rd Tuesday? You could do this:

First Tuesday =
MINX(FILTER('Calendar',WEEKDAY([Date],1) = 3),[Date])

[Anonymous]

Thanks @Greg_Deckler, your measure formula works well . Do you know if there is a way to mark a date asthe first tuesday of the month, within the Calendar table definition? I.e. Without having to create a separate measure?

[Greg_Deckler]

@Anonymous - So as a column you would need something like:

First Tuesday Column =
MINX(

  FILTER(

    'Calendar',

    WEEKDAY([Date],1) = 3 &&

      YEAR([Date])=YEAR(EARLIER([Date])) &&

      MONTH([Date])=MONTH(EARLIER([Date]))

  ),

  [Date]

)