Powerpivot to calculate value filtered based on its own table

[budiyantointernet]

Hi Everyone,
i'm new in powerpivot and still exploring on how to use DAX. I have a problem that currently i cant found how to do it in DAX.
Basically i have a table similar like this


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Datekey
[/TD]
[TD]Product Code
[/TD]
[TD]Store
[/TD]
[TD]This Week Inventory
[/TD]
[TD]This Week Sales
[/TD]
[TD]Inventory Week Supply
[/TD]
[/TR]
[TR]
[TD]20130601
[/TD]
[TD]Product A
[/TD]
[TD]X
[/TD]
[TD]10
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20130601
[/TD]
[TD]Product A
[/TD]
[TD]Y
[/TD]
[TD]20
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20130608
[/TD]
[TD]Product A
[/TD]
[TD]X
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20130608
[/TD]
[TD]Product A
[/TD]
[TD]Y
[/TD]
[TD]15
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20130615
[/TD]
[TD]Product A
[/TD]
[TD]X
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Inventory Weeksupply = this_week_inventory / average this_week_sales for next 3 weeks

i need to calculate the inventory weeksupply, meaning for example for datekey 20130601 for Product A in Store X the formula should be 10 / Average(4,3) = 10/3.5 = 2.85

What is the best approach to do this calculation ?

Thanks in Advance for sharing.

 

[masplin]

Why are you working out the weeks supply for 1st june using the weeks in the future as seems an impossible task i.e. you don't know the future? i could understand if you were working out how many weeks supply you have on june 16th based on the 3 previous weeks. is that what you meant?

My solution would be to use RANKX to rank the "this week sales" by store by product. This would give you a calculation for the most recent week, but do you need to know the number for the previous weeks as well? I'm guessing you are looking for a snap shot so you can see if all your stores have 3 weeks of stock across products? If you want a full historical table i'd have to give it more thought. let me know if the date order is as i think and if a snapshot is what you are after and I'll think u a formula.

Mike