Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Minkee
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.

 

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.

1 ACCEPTED SOLUTION
barritown
Super User
Super User

Hi @Minkee,

You can try such a calculated column:

barritown_0-1706817824780.png

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

View solution in original post

2 REPLIES 2
barritown
Super User
Super User

Hi @Minkee,

You can try such a calculated column:

barritown_0-1706817824780.png

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

Hi @barritown,

 

Many thanks for your help, much appreciated!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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