Recent Record based on Max Date

[sabeensp]

Hi,

I have a table called BaseTable, which has multiple record for each product, I need to create a new table which has only records of product with most recent date of each year.

Product A has 2 records in 2013 (2/1/2013 and 6/1/2013), I need only 6/1/2013 record and same for the other years. There are many other prodcuts, image only shows Prodcut A.

Appreciate your help.

[TomMartens]

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

[sabeensp]

@TomMartensWhen I try to declare var thisYear = 'BaseTable'[Year], it does not allow me to Pick "Year"

[v-piga-msft]

Hi @sabeensp,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[v-piga-msft · Accepted solution]

Hi @sabeensp,

By my tests, the calculated column of TomMartens is helpful. You could drag this column to visual level filter and only show the value yes.

For your problem, what's data type of your BaseTable'[Year]? Column or calculated column or measure?

Best  Regards,

Cherry

[TomMartens]

Hey,

can you please share a pbix file, upload the file to onedrive or dropbox and share the link.

Regards,

Tom

[edhans]

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.

[sabeensp]

@edhansProduct has 2 records in each year, I want the most recent date record of each year.