Calculate division per week based on a cumulative sum and average for the last x weeks

[jessicarocha]

Hello, 

my dataset is on a weekly base. I need to create a formula that uses the cumulative sum for the last completed 52 weeks of the comsumption and divides by the average of the stock for the last 52 weeks. The goal is to create the inventory turnover rate.

Example: 

Currently, we are on week 35 of 2024 (2024W35). Thus, I want to calculate the sum the comsumption from 2024W34 to 2023W35 (last 52 complete weeks). Then I want to do the average of the stock for the same time period. Then divide the sum of the comsumption by the average of the stock in order to obtain the inventory turnover rate.

During week 33, the time period changes to 2024W33 to 2023W34. During week 32, my time period changes to 2024W32 to 2023W33.

I also want to make sure that when there is not enought weeks (52) to calculate the cumulative sum or average, the result is empty. 

In excel, I create the formulas as I need (average_52_weeks_stock, sum_comsumption, inventory_turnover_rate). However, I am unable to do this in power bi. I don't even know from where to start. Could you please help me?

Dummy Power BI and Excel: 

PB and excel link 

[ThxAlot · Accepted solution]