Calculation Sales Last 12 months, context CalendarYearWeekISO, problem with weeks 52 and 53

[Anonymous]

Hi everyone,

I want to compare the Sales of the last 12 months weekly and then for the context CalendarYearWeekISO.

Datamodel:

Time Measures:

FirstDate TW = FIRSTDATE( 'Calendar'[FirstDateOfWeek])

FirstDate LTM -1W =
CALCULATE([FirstDate TW];
    FILTER(ALL('Calendar');
        'Calendar'[Year] = MAX('Calendar'[Year] ) -1 &&
        'Calendar'[WeekOfYearISO] - 1= max ('Calendar'[WeekOfYearISO])

FirstDate LTM -1W TEST =
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNumber = CALCULATE(max('Calendar'[WeekOfYearISO] ); ALL('Calendar') )
RETURN
CALCULATE([FirstDate TW];
    FILTER(ALL('Calendar');
        IF( CurrentWeek = 52;
           'Calendar'[WeekOfYearISO] = CurrentWeek -51 && 'Calendar'[Year] = CurrentYear;
           IF( CurrentWeek = 53;
           'Calendar'[WeekOfYearISO] = CurrentWeek -52 && 'Calendar'[Year] = CurrentYear;
           'Calendar'[WeekOfYearISO] = CurrentWeek + 1 && 'Calendar'[Year] = CurrentYear -1))))

LastDate TW = LASTDATE ( 'Calendar'[LastDateOfWeek])

FirstDate PP LTM -1W =
CALCULATE([FirstDate TW];
    FILTER(ALL('Calendar');
        'Calendar'[Year] = MAX('Calendar'[Year] ) -1 &&
        'Calendar'[WeekOfYearISO] = max ('Calendar'[WeekOfYearISO])
    )
)

LastDate PP LTM - 1W = DATEADD(LASTDATE('Calendar'[LastDateOfWeek]);-7;DAY)

As you can see in the following photo's, I have some null dates:

To compare weekly you have to know what the maximum weeknumber of the year prior was.

If the max. weeknumber lastyear = 52 than the calculations for the week 1 until 51 are as follows:

If the max. weeknumber lastyear = 53 than the calculations are:

 

I know, it also looks like mathematics to me but can someone help me with the correct Dax-formulas?

Thanks in advance,

Cor

PS: I don't have OneDrive or Dropbox and I don't know how to send a demo pbix-file.....

@Zubair_Muhammad

[v-chuncz-msft · Accepted solution]

@Anonymous,

There are only 52 weeks in year 2003. Also check if the column [Year] is correct.