Calculation for highest date for same ID value

[Minkee]

Hi,

I am developing a report in PowerBI Desktop.

When a value in the column ID is shown multiple times I want the last column to contain a '1' for the row with the highest enddate.

ID	Startdate	Enddate	Column value
1	1-5-2018	31-12-2019	0
2	1-1-2024	31-12-2024	1
2	1-1-2023	31-12-2023	0
3	1-1-2024		0

Been struggling with all kinds of formula's but haven't been able to find a solution.

[barritown · Accepted solution]

Hi @Minkee,

You can try such a calculated column:

DAX code in plain text for convenience:

Flag = 
VAR currentID = [ID]
VAR _tbl = FILTER ( Data, [ID] = currentID )
VAR maxDate = IF ( COUNTROWS ( _tbl ) > 1, MAXX ( _tbl, [Enddate] ) )
RETURN IF ( [Enddate] = maxDate && COUNTROWS ( _tbl ) > 1, 1, 0 )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Minkee]

Hi @barritown,

Many thanks for your help, much appreciated!