Create table with MAX year week

[Anonymous]

Need help to create a table containing products with MAX (latest) year week.  Not all products have the same MAX year week.  Desired results are below,

Source Table:        Results Table:

Year week  Product   Year week   Product

201848       A         201848      A

201848       B         201848      B

201847       A         201847      C

201847       B

201847       C

I am using Power BI Desktop - need a DAX solution or M code.  Maybe an "IsMax" flag and a filter..

[MattAllington · Accepted solution]

The DAX formula depends on the table structure. But assuming you have one table of data loaded in power BI  called Table then just do this

create a table visual on a report page

drag the Table[Product] Column to the table visual

write a measure = max(Table[Year Week])

place the measure in the table visual

[v-lili6-msft · Accepted solution]

HI, @Anonymous

After my research, you could use EARLIER to calculate the max year week for each Product and then mark an "IsMax" flag for filter.

For example:

TAG = IF( CALCULATE(MAX('Table'[Year week]),FILTER('Table','Table'[Product]=EARLIER('Table'[Product] )))='Table'[Year week],"IsMax","IsNotMax")

Then use this column to filter data.

Best Regards,

Lin

[MattAllington · Accepted solution]

The DAX formula depends on the table structure. But assuming you have one table of data loaded in power BI  called Table then just do this

create a table visual on a report page

drag the Table[Product] Column to the table visual

write a measure = max(Table[Year Week])

place the measure in the table visual