DAX column measure 1 for max date else 0

[cottrera]

Hi 

I have a table with lots of property references that apear multiple times but with different dates. I would like to create a calculated column that returns 1 for the max date relating to each of these references els return 0

Ref	Date	DAX - Max Date
132332	16/06/2011	0
132332	18/01/2014	0
132332	10/11/2015	0
132332	24/04/2017	0
132332	03/04/2020	0
132332	11/05/2020	1
90000	16/06/2011	0
90000	18/01/2014	0
90000	10/11/2015	0
90000	24/04/2017	0
90000	03/04/2020	0
90000	11/05/2021	1

thank you

RIchard

[cottrera]

Appologies Denis my data model was Direct Query. When I changed it to Import your measure worked fine 😁

[cottrera]

Hi Denis thank you for your quick reponse. I am getting these errors when adding the code to a column measure

Richard

[selimovd · Accepted solution]

Hey @cottrera ,

try the following calculated column. I added some explanation in the code:

MaxDate =
-- Calculate the MAX date for the current REF-ID
VAR vMaxDate = CALCULATE ( MAX ( myTable[Date] ), ALLEXCEPT ( myTable, myTable[Ref] ) )
RETURN
    -- When the date of the current row is equal to the vMaxDate then return 1 otherwise 0
    IF (
        myTable[Date] = vMaxDate,
        1,
        0
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic