Forum Discussion
Solved
New Table based on maxdate
Hi,
I am trying to create a table of unique product codes and latest date using "New table" in Power BI. I need this so I can use the product code in a relationship.
So e.g ProductTable1
AG00001 16/10/2018
AG00001 15/10/2018
AG00001 14/10/2018
AG00002 10/10/2018
AG00002 12/10/2018
ProductTableLatest
AG00001 16/10/2018
AG00002 12/10/2018
If I use SELECTCOLUMNS as below I get 31/12/2018 for all dates.
ProductTableLatest = SELECTCOLUMNS(ProductTable1,"LatestDate",MAX(ProductTable1[ProductDate].[Date]), "Serial Number", ProductTable1[SerialNumber])
Appreciate any help !
marculos Please try this as "New Table"
ProductTableLatest = SUMMARIZE(Test12InputCalcTable,Test12InputCalcTable[Product],"Latest",MAX(Test12InputCalcTable[Date]))
3 Replies
marculos Please try this as "New Table"
ProductTableLatest = SUMMARIZE(Test12InputCalcTable,Test12InputCalcTable[Product],"Latest",MAX(Test12InputCalcTable[Date]))
wow you guys are fast! many thanks for all your help :) :)
This should work:
ProductTableLatest = SUMMARIZECOLUMNS ( ProductTable1[SerialNumber], "LatestDate", MAX ( ProductTable1[ProductDate].[Date] ) )
