Display Only those records where date is latest

[NG1407]

I want to display data only for those student where date is latest. I dont want to show old dates associated with that student.

I want to do it in power bi /dax

PFB data:

Student	startdate	enddate
A	1-jan-2022	10-dec-2022
A	1-jan-2022	25-jul-2023
A	1-jan-2022	15-jan-2025
B	4-apr-2023	10-dec-2022
B	4-apr-2023	11-dec-2022
B	4-apr-2023	25-jul-2023

Result I want

Student	startdate	enddate
A	1-jan-2022	15-jan-2025
B	4-apr-2023	25-jul-2023

[vivek31 · Accepted solution]

HI @NG1407 ,

you can create this type of measure to find latest date

latest date = CALCULATE(MAX('Table (4)'[enddate]),ALLEXCEPT('Table (4)','Table (4)'[Student]))

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you

[NG1407]

You solution works well when we have same records. It doesn't work when we have different records. As shown below

Result its giving...

Result it should give is: Latest date records only i.e. 

Student	date	Remarks
A	2/3/2023	Good
B	2/3/2025	Good

[sangeethray_92]

@NG1407 

Create a calculated column in the table to flag the first date of each student.

Last_Date_Flag =
VAR A =
CALCULATE(
MAX('Table'[EndDate]),
ALLEXCEPT('Table', 'Table'[StudentID])
)

RETURN
IF(A = 'Table'[EndDate], 1, 0)

Put this column to the visual level filter and filter 1. This way you will be able to show any data related to the last date for each student. 

Did I answer your question? Mark my post as a solution! And Kudos are appreciated

[sangeethray_92]

@NG1407 

Have you tried this?

[NG1407]

Yes this works fine. But its affecting other Dax too.

[sangeethray_92]

Can you show an example? 
When visualizing how it is effecting other measures? 

May be it will get fixed if you ignore this column in other measures. Adding all with this new column in other measures. 

[bhanu_gautam]

@NG1407 , Go to modelling and create a new table

LatestRecords =
VAR LatestDates =
ADDCOLUMNS(
SUMMARIZE(
YourTable,
YourTable[Student],
"LatestEndDate", MAX(YourTable[enddate])
),
"startdate",
CALCULATE(
MAX(YourTable[startdate]),
YourTable[enddate] = [LatestEndDate]
)
)
RETURN
SELECTCOLUMNS(
LatestDates,
"Student", [Student],
"startdate", [startdate],
"enddate", [LatestEndDate]
)