Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

SAMEPERIODLASTYEAR - working with week ending dates

I have a dataset that goes by week ending dates e.g. every sale made between 01/10/2018 - 07/10/2018 (dd-mm-yyyy) will have the date 07/10/2018 (last day of week 40 in 2018).

When I use SAMEPERIODLASTYEAR I obviously get results for 01/10/2017 - 07/10/2017 which is technically correct.

 

The issue with this is, the weeks in the range don't quite align to how I want to view LY.

See below image, the week ending date is highlighted in yellow for TY & LY. Week ending for LY means I essentially am comparing 28/09/2017 - 01/10/2017 (e.g. the prior week LY) against 01/10/2018 - 07/10/2018 (current week TY).

 

 

I would like to, ideally, determine which week majority of the LY date range sits in, then adjust the LY week/date-range accordingly.

e.g. in this scenario, 01/10/2018 - 07/10/2018 should look at 02/10/2017 - 08/10/2017 (or week ending 07/10/2018 is equivalent to week ending 08/10/2017 LY).

 

Please let me know if any of this is confusing and I can try and clarify it better.

 

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support

    Hi jd009,

     

    "I would like to, ideally, determine which week majority of the LY date range sits in, then adjust the LY week/date-range accordingly."

     

    <--- The logic is not so clear. Could you share some mock-up and show more details about you expected result and the current measure you are using?

     

    Regards,

    Jimmy Tao

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support

    Hi jd009,

     

    Have you solved your issue by now?

     

    Regards,

    Jimmy Tao