Forum Discussion

sabeensp's avatar
sabeensp
Helper IV
7 years ago
Solved

Recent Record based on Max Date

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 ...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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's avatar
TomMartens
Super User
7 years ago

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's avatar
sabeensp
Helper IV
7 years ago

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

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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's avatar
    TomMartens
    Super User
    7 years ago

    Hey,

     

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

     

    Regards,

    Tom

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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