Forum Discussion
Join a date interval to a date table
Hi,
I need help with following problem and the picture below illustrates what I have in terms of tables and what I want to accomplish (but haven't so far).
Our purchasing department want to se what price in store we had on our products on the date of purchase.
Step 1 is to take out the purchasing information: Product, date and price.
Step 2 is to take out the price list with date intervals that shows our price in store.
Step 3 is to do the necessary joins but here is where i run into problems: how do join the store price table with DimDate?
As seen in the picture I want to store price which date interval corresponds to the purchasing date but I have no idea how to do it.
Would greatly appreciate some help! 🙂
5 Replies
- amitchandak
Super User
Anonymous , you can create a new column in the purchase using storesales
maxx(filter(storesales , storesales [StartDate] <= purchase[purchase Date] && purchase[purchase Date] <= storesales [endDate] ) storesales [Price in store])
Thanks a lot for taking the time!
Two questions:
1. Should I join the storeprice table with the date table somehow (please see my picture).
2. I put in your formula (without doing any more joins than in my original post) but it does not give me what I want. Are you sure that your formula is supposed to work?
Best regards!
And also many dates to consider. For each row in the purchasing table there is a certain price from the store table that is correct. Your formula only seems to search for the highest price, not taking into account the date and products. amitchandak
