Weeks management in a set analysis

[fayzzamzam]

Hi

I have been working on a set analysis for a straight table to calculate the sum of sales in the current week and the week before and two weeks before then the same current week but from last year and two weeks back plus 3 weeks forward

in total six weeks from last year and three from this year

everything went well with the following formula 

sum({<week=,Month=,year=,WeekCounter={$(=only({1<Date={"$(=date(NumMin(Max(Date),Today()-1)))"}>}WeekCounter)+$1)}>}[sales])

now the problem is the labels of each column of the set analysis should give the week number of the formula

I have this formula to calculate the current week number

only({1<Date={"$(=date(NumMin(Max(Date),Today()-1)))"}>}YearWeek) --> it gives for example 201850

the problem is if the user choose week 1 at 2018 then one week back become 201752 or 201753 or 201701 depending on the date.

how can I make week subtraction dynamic and avoid the problem of week one and week 52, 53

[marcus_sommer · Accepted solution]

You need to use a continuous weekcounter which might be created with an autonumber() over year and week or with something like year * 53 + week. You may need one or two adjustments to this main-approach depending on how the weeks are defined (any ISO or something customized).

- Marcus