Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
1 year ago
Solved

Latest Date by Column Value

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

Need Help
  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    1 year ago

    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

  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    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

4 Replies

  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    Super User
    1 year ago

    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

  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    1 year ago

    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.
  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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