Get item price based on order month-year

DAX DAX Calculations
1

Hi,

I need help with getting the correct item price based on the order month - year.

I have an order table and the itemPriceChange log table. I need to grab the price based on the order month -year.
For example, Item1 price was $2 in Jan, 2022 and it changed to $4 in Feb, 2022 and it did not change after Feb. So, the current price is $4. But there is an order in the system that got placed in Jan, 2022. So, the item price should be $2 for that order.

If the price changed more than once in a month, I need to grab the Max price.

How can I grab the price based on the order month-year in DAX?

I have attached the pbix and excel file.

Book2.xlsx (50.6 KB)
ItemPrice.pbix (789.1 KB)

I would really appreciate the help on this…
Thanks.

2

Hi @EnterpriseDNA ,

Any guidance on the above issue will be appreciated…

Thank you…

3

@gkhokher ,

The main issue here is your data model. I had to rework the tables such that they related based on the date fields. Also, please be sure to mark your Calendar table as a date table, otherwise your time intelligence measures may not work.

I constructed this measure using a virtual table, so that I could test whether the number of rows were greater than or equal to zero. If they were equal to zero that meant no price match was found and I returned that result. Otherwise I returned the max value of the filtered virtual table.

Here’s the measure that does the heavy lifting. The other measures simply harvest the slicer values for month and year.

image
image1487Ă—547 58.7 KB

Here’s how it looks in action:

image
image2121Ă—772 49 KB

image
image2104Ă—776 87.4 KB

I hope this is helpful. Full solution file attached below.

1 Like
4

Hi @BrianJ ,

Thank you very much for the solution!
I am just one step away from the solution. I need to get the price for each item in a specific time frame. Right now, it is just filtering by date and giving us that price, we should be able to get the price based on the items and date.
For example, Item1 price is $1 in Jan, 2022, Item2 price is $2 in Jan, 2022 and so on…

Could you please help with that?

Thank you very much!

5

Hi @gkhokher, it’s great to know that you are making progress with your query. Please be reminded that asking more than one question in a forum thread and asking question after question in the same forum thread around the same project or piece of development work is considered inappropriate.

Kindly mark this thread as solved and create a separate thread for your other questions related to this inquiry so that other users can easily check the details of your queries.

For further questions related to this post, please make a new thread. More details can be found here - Asking Questions On The Enterprise DNA Support Forum