Find correct SUM value of inventory before a date

N

nav73

New member
Joined
Jan 21, 2023
Messages
4
Reaction score
0
Points
1
Excel Version(s)
365
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.

1674325572596.png


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

1674325594352.png


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

1674325607211.png


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

1674325642989.png


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

1674325691937.png


So far I have only this

1674325717884.png

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?

1674325737016.png

Please see a link to Excel sheet: Click here

Thanks,


Naveen
 
Last edited:
nav73 said:
PLEASE BE INFORMED THAT I WANT TO HAVE THE SOLUTION IN EXCEL NOT POWER BI
Click to expand...
Is a solution involving Get & Transform Data in the Data tab of Excel (aka Power Query) which is built-in since Excel 2016, allowed?
I can get the same result as you using Power Query, but I'm not sure that it's what you want; you're using the same price on one given date for all product sold before that date. Is that really what you want? I think the same applies to your fx conversion rates.

1674408530514.png

ps. It would help if you were to attach (or link to) your file in this thread.
pps. cross posted without links:
learn.microsoft.com

Find correct SUM value of inventory before a date - Microsoft Q&A

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…
learn.microsoft.com
community.powerbi.com

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...
community.powerbi.com community.powerbi.com
@nav73 , for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of https://excelguru.ca/a-message-to-forum-cross-posters/
 
Last edited:
Hi p45cal,

Thanks for your response.

  • Cross-posted
I have read the post re. cross-post. I really was not aware of this, to my (weak) defense. I was really fed up after trying to get the solution to my problem so I just did what I did and never meant to cause any disrespect/offense and will make sure this does happen again.
Since you have now put a link in the response does that mean I do not have to do this any longer?

  • “Is a solution involving …. allowed?”
For now, that would be good but I want to have this in DAX so that I can include a date slicer that connects the PowerPivot and every time I change the day I get the overview of that particular date.

  • “you're using the same price on …… the same applies to your fx conversion rates.”
Yes. So what I am trying to achieve is the value of the inventory. On a specific date, the inventory of a product will be the sum of that product in the inventory multiplied by the market value of that date. Then multiply with the FX rate at that date, depending on whether it is EUR, USD (and in the future JPY, etc., etc.)

Thanks so much for your help

Naveen
 
nav73 said:
Since you have now put a link in the response does that mean I do not have to do this any longer?
Click to expand...
As long as that's the only place you've asked that question.
An attachment here of your file, or link to your file, would be useful.


nav73 said:
For now, that would be good
Click to expand...
See attachment for Power Query offering. You'll need to refresh it like a Pivot.
It looks at cell B3 of the report sheet.
(The table on the Date_data sheet (not used in this PQ) has been disconnected from its query (I haven't access to the csv file here).


nav73 said:
am trying to achieve is the value of the inventory
Click to expand...
OK, understood.

I'll explore the DAX side of things later.
 

Attachments

  • Chandoo11690new data.xlsx
    600.3 KB · Views: 1
Hi all,

Thanks for the Excel sheet. I will look at it later today. Meanwhile, I have put in a link to the sheet as requested. I thought I put it in first time around so was surprised that it was asked for. Sorry about that!

Thanks,

Naveen
 
You must log in or register to reply here.
Back
Top