Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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

  • Anonymous,

     

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

1 Reply

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support

    Anonymous,

     

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