Forum Discussion

Jeffuh's avatar
Jeffuh
New Member
4 years ago
Solved

Creating a Filter for a Specific Week

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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/

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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