Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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.
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.
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.
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.
@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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |