Latest Date by Column Value

[M_SBS_6]

Hi, 

I have several columns in my table and I'd like to flag a 1 or 0 in another column based on the latest date by product. Any idea how to do this please? 

ID.  Date.         Product.   Flag

1.   11/01/23.     AD.           0

2.   11/01/23.     AD.           0

3.    06/08/24.   AD.            1

[Jai-Rathinavel · Accepted solution]

@M_SBS_6 Create a calculated column using the below DAX

Flag = 
var maxdate = CALCULATE(MAX(MyTable[Date]) , ALLEXCEPT(MyTable,MyTable[Product]))
RETURN
IF( MyTable[Date] = maxdate , 1 , 0 )

Output:

Did I answer your question ? If yes, please mark my post as a solution.

Thanks,

Jai

[Anonymous]

Hi @M_SBS_6 ,

Thanks for all the replies!
Please check to see if these replies can help you. If solved please accept the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

[Kedar_Pande · Accepted solution]

@M_SBS_6 

New column:

Flag = 
VAR LatestDate = 
CALCULATE(
MAX('Table'[Date]), 
FILTER(
'Table', 
'Table'[Product] = EARLIER('Table'[Product])
)
)
RETURN
IF('Table'[Date] = LatestDate, 1, 0)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Selva-Salimi]

Hi @M_SBS_6 

you can write a column as follows:

Column = var max_date = calculate(max('Table'[Date]) , filter('Table' , 'Table'[Product]=EARLIER('Table'[Product])))

return

if (max_date='Table'[Date] , 1 ,0)

If this post helps, then I would appreciate a thumbs up 👍and mark it as the solution to help the other members find it more quickly.

[Jai-Rathinavel · Accepted solution]

@M_SBS_6 Create a calculated column using the below DAX

Flag = 
var maxdate = CALCULATE(MAX(MyTable[Date]) , ALLEXCEPT(MyTable,MyTable[Product]))
RETURN
IF( MyTable[Date] = maxdate , 1 , 0 )

Output:

Did I answer your question ? If yes, please mark my post as a solution.

Thanks,

Jai