cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## 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.

 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.

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Regular Visitor

Hi @barritown,

Many thanks for your help, much appreciated!

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors