Im Mathematically challenged - comparing weeks and month from last year

[bilingual]

Hi, hope somebody can help me , right now i compare september 2020 with september 2019, the difference is 12,6 percent, so far so good. 

However as i also make a week to same week last year comparison, and i can not understand why the numbers differ:

The difference per week

Week 36	14 %
Week 37	15 %
Week 38	15,6 %
Week 39	16,3%
Week 40	16 %

I know that months and weeks are not the same last year, and there is an offset of days in the weeks, but nevertheless i cant mathematically understand why the month has a lower percentage than the average of the weeks.  When i compared day to day and sum it up, it also gives me 12,6 percent.

Please help a mathematically challlenged person 🙂

[sfaizee]

Hi,

Just have a look at your data numbers will give you some idea on the reason.

For simplification; try to imagine the entire month as a 100 mile road trip.

wk#1 is 1-25 miles - was a highway ride; smooth sailing

wk#2 is 26-50 miles - was complete uphill; low value

wk#3 is 51-75 miles - downhill slope; high values

wk4 - 76-100 miles - dirt road; swing values.

Now if you look at  wk#3 - you average will be completely off. 

I hope this helps.

[v-stephen-msft · Accepted solution]

Hi @sfaizee ,

Please let me explain to you with an example.

Suppose there are four weeks in January 2020, and four weeks in January 2019, data are as follows.

year.month	week	value
2019.1	1	1
2019.1	2	3
2019.1	3	5
2019.1	4	7
2020.1	1	2
2020.1	2	4
2020.1	3	6
2020.1	4	8

week1=(2-1)/1=100%
week2=(4-3)/3=33.33%

week3=(6-5)/5=20%

week4=(8-7)/7=14.29%

average week=(100%+33.33%+20%+14.29%)/4=41.905%

Actually, average week={(4-3)/3+(6-5)/5+(8-7)/7}/4=41.905%

month=(2+4+6+8-1-3-5-7)/(1+3+5+7)=4/16=25%

The numerator and denominator of the two formulas are different, so the results are different.

Hope you can understand.

Best Regards,

Stephen Tao

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

[v-stephen-msft]

Hi @bilingual ,

Because the difference of the sum of different weeks and the difference of the sum of different months are different. It may be that the weekly fluctuation is large, so the difference will become larger.

Best Regards,

Stephen Tao

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

[amitchandak]

@bilingual , have a separate week/ date table and create rank on weekstart date ot YYYYWW and use that like given below

New column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

new measure
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[bilingual]

Hi amit, i did the calcualtion in Power BI and it is correct, but i cant gras why the difference is so significant when you compare months to weeks.