cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Find correct SUM value of inventory before a date

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?

Thanks,

Naveen

0 REPLIES 0

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors