Find correct SUM value of inventory before a date

[naveen73]

PLEASE BE INFORMED THAT I WANT TO HAVE THE SOLUTION IN EXCEL NOT POWER BI

Hi all,

I have a dataset and want to perform some calculations with a measure. To this end, I have changed the AdventureWorks dataset (publicly available) to make my problem more understandable.

I have the following tables:

• Product_Price

This table lists the price of a product at a specific date. I made a unique column, which consists of the product key and the date.

• Product_Static

This table lists the details of a product key: name of the product and the color.

• FX

For each day and for each currency, the conversion rate.

• Sales

For each product (ProductKey) sold, the date and quantity. Column B makes the line unique: =C2&E2.

What I have so far in Excel is a prototype see screenshot
- in cell E3 we have the formula =UNIQUE(Sales!C2:C41,FALSE,FALSE). This populated the column with all the products that are sold

• in cell B3 I have a dropdown with the date.

When a date is selected in B3, the following happens:

• Column F: =VLOOKUP($E3,Product_static!$A:D,2,FALSE) The product name of Productkey in column E is displayed.
• Column G: =VLOOKUP($E3,Product_static!$A:E,3,FALSE) The color of Productkey in column E is displayed.
• Column H: =SUMIFS(Sales!D:D,Sales!C:C,E3,Sales!E:E,"<="&$B$3). The number of products of Productkey that are sold up until the day (and included that day) selected in cell B3.
• Column I =VLOOKUP(E3&$B$3,Product_Price!B:E,4,FALSE): the price of ProductKey for that day is selected.
• Column J =VLOOKUP(E3,Product_Price!D:F,3,FALSE): the currency for the Productkey is displayed
• Column K =I3*H3: the sales amount in the currency is calculated.
• Column L: =VLOOKUP(J3&$B$3,FX!A:D,4,FALSE)*K3: the sales in GBP is calculated

So far I have only this:

It shows only that day. 

Total_Sales is a measure (and the only measure I have so far, 

total_sales = SUMx(Sales,Sales[Quantity]*RELATED(ProductPrice[Price]))

Also, the FX table does not connect automatically to any of the other tables. Why is that?

Please see a link to Excel sheet:  Click here 

Thanks,

Naveen