Last 5 week sales

[Markando]

Hi

I've got a date table with date and Weeknumber. Now I want to create a calculated field that marks all dates that are in the current last 5 weeknumbers. The formula below works fine for now, but I know it wont work when we change year and we start over on week 1. Do you have any suggestions how I can make this formula future proof? 

The formula is getting the actual weeknumber for today, then do the IF for all dates where week => Week-number today -5.

=

IF(Calender[ISOWeekOfYear]>=
CALCULATE(MAX([ISOWeekOfYear]);
FILTER(Calender;
Calender[Date]=TODAY())
)-5

&&calender[Date]>TODAY()-60

&&calender[Date]<=TODAY()

;"Yes";"No")

[Peter_R]

You may find it easier to do this on the query side, adding a column to the date table, taking the difference between days. A step like this will do the calculation:

IsLast5Wks = Table.AddColumn(**previous step**, "IsLast5Wks", each if Duration.Days(EndDate - [Date]) > 35 then false else true, Logical.Type ),

... if you're trying to flag all of the dates from the week of 5 weeks back, the two dates you plug into the formula would need some extra work to calculate the ending date of the week - a previous step like this would do it:
WeekEndingDate = Table.AddColumn(** previous step **, "WeekEndingDate", each Date.EndOfWeek( [Date], Day.Monday ), Date.Type ),

.. then use [WeekEndingDate] in place of [Date] in the first step.  the 'EndDate' in the first step could either be today's date, or else a calculation of the week ending date from today's date. In any case, the key point is that the date difference calculation avoids the year wrapping problem that trying to perform the test working with a year+week field has.