Forum Discussion
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-msftCommunity 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-msftCommunity Support
Hi jd009,
Have you solved your issue by now?
Regards,
Jimmy Tao