none
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 ?

    Thanks in Advance for sharing.

    Thursday, June 27, 2013 1:28 AM

Answers

  • 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