Recent Record based on Max Date

Hey,

I'm not sure if it is sufficient if you just create a new calculated column in your base table like:

is most recent = 
var thisYear = 'BaseTable'[Year]
var thisProduct = 'BaseTable'[Product]
var maxDateByProductAndYear = 
CALCULATE(
    MAX('BaseTable'[Date])
    ,ALL('BaseTable')
    ,'BaseTable'[Product] = thisProduct
    ,'BaseTable'[Year] = thisYear
)
return
IF(AND(
    AND('BaseTable'[Product] = thisProduct,'BaseTable'[Year])
    ,'BaseTable'[Date] = maxDateByProductAndYear)
    ,"yes"
    ,"no"
)

This looks like this

Now it's possible to filter the data by this column. I guess that this solution does not need as much money as a new table, but this may depend on how many rows are available in total. But nevertheless, personally, I try to avoid materializing tables and instead create calculated columns to filter down rows.

Hopefully, this gets you started.

Regards,

Tom

Tom

I don't undersand what you want. You say product A has 2 records in 2013, but in your base table it has 10 records based on the date. What is the "Year" column telling us that the "Date" column is not? In other words, why is the year 2017 but the date is 9/1/2013 for the last record?

Then you say "I need only 6/1/2013 record and same for the other years" but then your summary table has 8/1/2013, 9/1/2013, etc in it.

I cannot figure out the logic to get from Base to Summary in my head, so I certianly cannot come up with DAX or M to get you there at this point.