# Powerpivot to calculate value based on the table field

### Question

• 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

Datekey      ! product code | store  | this_week_inventory | this_week_sales  | Inventory Weeksupply

20130601   | Product A       |   X      |  10                             | 2                           |    = this_week_inventory / average this_
20130601   | Product A       |   Y      |  20                             | 1                           |       week_sales for next 3 weeks
20130608   | Product A       |   X      | 6                                | 4                           |
20130608   | Product A       |   Y      | 15                              | 5                           |
20130615   | Product A       |   X      | 3                                | 3                           |

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 ?

Thursday, June 27, 2013 1:28 AM

• Hi SI_budiyanto,

if you want to calculate average for next 3 week. You must have the week number and datekey is formated by date value.

I have make a small sample and share it on my skydrive

https://skydrive.live.com/#!/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A!122&app=Excel

I hope this will help with you.

Regards,

Thursday, June 27, 2013 1:15 PM

### All replies

• Hi SI_budiyanto,

if you want to calculate average for next 3 week. You must have the week number and datekey is formated by date value.

I have make a small sample and share it on my skydrive

https://skydrive.live.com/#!/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A!122&app=Excel

I hope this will help with you.

Regards,

Thursday, June 27, 2013 1:15 PM
• Hi CDzung,

thank you so much, its really helpful :)

Thursday, June 27, 2013 1:44 PM