Creating a Filter for a Specific Week

[Jeffuh]

Good Evening:

I am trying to create a report that shows historical data for sales for a specific week, in this case Mother's Day.  My report has all the sales data for each year, so I am trying to filter it down to just that 1 week.  The issue is, unlike say Valentine's Day, Mother's Day is always the second Sunday of May, so I am trying to create a filter for the 1st Sunday of May through the 2nd Sunday of May.

I feel like this should be a simple fix, but I am just overlooking something.

[Anonymous · Accepted solution]

HI @Jeffuh,

You can create a  calculated column with the following formulas to check the Second Sunday of May and return the tag:

2nd Sunday of May=
IF (
    //the second week of May
    WEEKNUM ( Table[Date], 2 )
        = WEEKNUM ( DATE ( YEAR ( Table[Date] ), 5, 1 ), 2 ) + 1
        && //sunday
        WEEKDAY ( Table[Date], 2 ) = 7,
    "Y",
    "N"
)

Regards,

Xiaoxin Sheng

[VahidDM]

Hi @Jeffuh 

You can create a Date Table, and add a column to it as a Mother's Day column, and add YES to those dates you want to be included to your report and NO for other dates.

Then, use this Date table and Mother's Day column to filter your report.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/