Advanced filtering of rows to show price of "current" product

[Anonymous]

Hi,

I got a price table structured like the one below. I need a dynamic DAX statement that will be used to plot the price on a line chart. It needs to plot the rows with the lowest ProductYear TODAY or the last date. In the example below, I would want to only show the price for 2019, since 2020 values have started to tick in and values for 2018 are no longer being reported.

Date	Product	ProductYear	Price
01.01.2018	JAN-18	2018	20,2
01.01.2018	JAN-19	2019	22,15
02.01.2018	JAN-18	2018	20,5
02.01.2018	JAN-19	2019	22,55
03.01.2018	JAN-19	2019	22,7
03.01.2018	JAN-20	2020	20,22

In other words, the data I would want to plot if today was 02.01.2018 would be this

Date	Product	ProductYear	Price
01.01.2018	JAN-18	2018	20,2
02.01.2018	JAN-18	2018	20,5

And if today was 03.01.18 I would want this to be plotted.

Date	Product	ProductYear	Price
01.01.2018	JAN-19	2019	22,15
02.01.2018	JAN-19	2019	22,55
03.01.2018	JAN-19	2019	22,7

Thanks for any help  🙂

[Anonymous]

Hi,

I got a price table structured like the one below. I need a dynamic DAX statement that will be used to plot the price on a line chart. It needs to plot the rows with the lowest ProductYear TODAY or the last date. In the example below, I would want to only show the price for 2019, since 2020 values have started to tick in and values for 2018 are no longer being reported.

Date	Product	ProductYear	Price
01.01.2018	JAN-18	2018	20,2
01.01.2018	JAN-19	2019	22,15
02.01.2018	JAN-18	2018	20,5
02.01.2018	JAN-19	2019	22,55
03.01.2018	JAN-19	2019	22,7
03.01.2018	JAN-20	2020	20,22

Thanks for any help 🙂

[v-juanli-msft]

Hi

it seems the condition " the lowest ProductYear TODAY or the last date" and "only show the price for 2019" are contradictory.

i used the example dataset and get the result as the first picture based on the condition of "lowest productyear and last date from today".

Could you help me more clear about the criterion to get the expected table?

Best Regards

Maggie

[Anonymous]

I think you got a today mixed into that example data by the way, since I don't think I listed a row with the date 4/27/18 

[v-juanli-msft]

Hi @Anonymous

After struggling with this problem, I finally figure out a solution, here is my pbix for your reference.

Best Regards

Maggie

[Anonymous]

Sorry for the late reply!

I'll simplify it a little, so the criterion would be as follows.

Based on the latest date in the "Date" column, I need to plot the price for the rows that contain the lowest productyear.

Every date will have several rows for different products. 2018, 2019, 2020 etc. These can be traded like a future, but they will become untradeable at some point in time. When 2018 is no longer tradeable, there will be no new rows coming into the dataset, but the old rows will remain and need to be filtered out. Which is what happens between 02.01.18 and 03.01.18 in my example. The "active rows" goes from being the ones with 2018 to 2019. (The date formating in this example is DDMMYY btw).