topic Re: Display Only those records where date is latest in DAX Commands and Tips

Display Only those records where date is latest

NG1407 — Wed, 05 Feb 2025 15:30:53 GMT

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

Re: Display Only those records where date is latest

bhanu_gautam — Wed, 05 Feb 2025 15:36:14 GMT

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]
)

Re: Display Only those records where date is latest

sangeethray_92 — Wed, 05 Feb 2025 20:39:24 GMT

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

Re: Display Only those records where date is latest

vivek31 — Wed, 05 Feb 2025 17:40:56 GMT

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

Re: Display Only those records where date is latest

NG1407 — Tue, 11 Feb 2025 10:59:58 GMT

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

Re: Display Only those records where date is latest

sangeethray_92 — Tue, 11 Feb 2025 11:03:08 GMT

NG1407

Have you tried this?

Re: Display Only those records where date is latest

NG1407 — Tue, 11 Feb 2025 14:47:53 GMT

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

Re: Display Only those records where date is latest

sangeethray_92 — Tue, 11 Feb 2025 16:20:25 GMT

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.