how to create new table in power bi depended on Max date from another table

liliy 0
Viewed 37

In powerbi I have table42 with 3 columns ( productId , amount , date) i need to creat a new table depends on this table(table42) with filter return only max date for each product and amount so i can calaculate the total amount

below my condation in sql

case when d.Date=(SELECT max(z.Date) FROM table42 zz WHERE d.prdAcctId=zz.prdAcctId)

I tried this but I don't get any result

= #table({"prdAcctId","date", "amount"},{table42[prdAcctId],table42[date],DATEVALUE(MAX('Table42'[DATE]))}

thanks in advance.

dax powerbi-desktop powerbi
1 Answers

Try this

= FILTER(
     table42
    ,[date]=CALCULATE(MAX(table42[date]),ALLEXCEPT(table42,table42[prdAcctId]))
    
  )
userLOTh10
Related