Date calculations for PY with month shift

[Alisea_MI]

Hi!

I would appreciate some help on the following date calculation case.

I have a slicer, where one can choose a period: 13 weeks, 26 weeks or 52 weeks.

I need to calculate a period selected in the slice for the PY in the following way:
1) Take today's date and move it to the last year: e.g. 2021-12-03 -> 2020-12-03 (the formula works)

2) Find the last date for the complete calendar week preceding this date. e.g. 2020-12-03 was Thursday. So the preceding calender week should end with 2020-11-29.

The formula I use:


This formula that works well while we need to find the preceding week in the same month.

However, as in my case above, when the preceding week is in the previous month, it does not move the month, so the end date that I get is 2020-12-29 instead of 2020-11-29.

Would appreciate any tips / help on how to manage the month issue: to make it move, if the preceding calender week was in the last month.

Many thanks in advance!

[v-angzheng-msft · Accepted solution]

Hi, @Alisea_MI 

Try to add a conditional judgment to the month of the return value.

The modified measure might like this:

RETURN
    DATE ( YEAR ( _END ), IF ( DAY ( today ) > 7, MONTH ( _END ), MONTH ( _END ) - 1 ), DAY ( _END - _DAYNUM ) )

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You may want to use EDATE(TODAY(),-12)  instead.

NOTE:  TODAY() has very different meaning in Import Mode and Direct Query Mode.  Careful!

Your var _DATE is not used anywhere.

If I were you I would use a proper calendar table with Weekday numbers already included. 

NOTE:  You talk about months but your formulas seem to be based on weeks. These things are not compatible.