Forum Discussion

Minkee's avatar
Minkee
Regular Visitor
2 years ago
Solved

Calculation for highest date for same ID value

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.

 

IDStartdateEnddateColumn value
11-5-201831-12-20190
21-1-202431-12-20241
21-1-202331-12-20230
31-1-2024 0

 

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

Need Help
  • barritown's avatar
    barritown
    2 years ago

    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

2 Replies

  • barritown's avatar
    barritown
    Solution Sage
    2 years ago

    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