Weekly sales average over X number of weeks based on Current Week - 4 weeks

[Junminkim0214]

Hello Power BI expert heroes!

I'm trying to calcuate weekly consumer sales average over 4 weeks based on the table format below (My table have month and week only, but not date)

Could you please let me know how I can get the results? 

Calendar Month	Calendar Week	Consumer Sales
Jan	1	50
Jan	2	60
Jan	3	80
Jan	4	50
Feb	5	70
Feb	6	50
Feb	7	48
Feb	8	30
Mar	9	40
Mar	10	46
Mar	11	90
Mar	12	72
Mar	13	76

@amitchandak @parry2k 

[parry2k]

@Junminkim0214 year? What happens if there are two years of data, month and week will repeat? How you will find out which year it is and go back 4 weeks?

[Junminkim0214]

Hi @parry2k ,

First, SUPER Thanks for your help! I pasted the same measures to my woring file, and it works now. 👍👍

Regardng year, yes you're right. How do we need to calculate 'Ave Last 4 Weeks' especially for 2020 early Jan which required LY 2019 Dec Weeks? 

Calendar Year	Calendar Month	Calendar Week	Consumer Sales
2019	Dec	48	80
2019	Dec	49	50
2019	Dec	50	70
2019	Dec	51	50
2019	Dec	52	48
2020	Jan	1	50
2020	Jan	2	60
2020	Jan	3	80
2020	Jan	4	50
2020	Feb	5	70
2020	Feb	6	50
2020	Feb	7	48
2020	Feb	8	30
2020	Mar	9	40
2020	Mar	10	46
2020	Mar	11	90
2020	Mar	12	72
2020	Mar	13	76

[amitchandak]

@Junminkim0214 , I am hopeful, you have already got solution from @parry2k  post. My approch is using Rank. In that you need a numeric month. 

Year-Month-Week 

2019-01-01

You can refer 

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

As I have nor seen Parry's Solution, my be repeated what he has suggested. 

[parry2k · Accepted solution]

@Junminkim0214 based on your data, the solution is attached, tweak it as per your need. I created few measures to showcase how it works but you can collapse all these in one (if you want)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡