Forum Discussion
Create table with MAX year week
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..
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
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
2 Replies
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
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