Need to getting latest date rows only against duplicate dates and unique dates

[AmitSaini]

Hi Team,

I need your help to get the desired output in Power BI DAX.

Below is my dataset:

ProjectName	RunId	DateTime	TotalTest	CoveredLine	TotalLine
Sky	464545	09/19/2024	787	6753	7611
Sky	466518	10/03/2024	787	6753	7611
Sky	468720	10/18/2024	795	6837	7693
Sky	470858	11/05/2024	795	6844	7699
Sky	474794	12/02/2024	808	6933	7790
Sky	475678	12/06/2024	808	6932	7790
Sky	480882	01/16/2025	815	6974	7826
Spark	462901	09/11/2024	1246	0	0
Spark	463000	09/11/2024	1246	34712	52198
Spark	466141	10/01/2024	1246	34719	52254
Spark	467175	10/08/2024	1274	35037	52745
Spark	474420	11/28/2024	1270	35033	52737
Spark	474734	12/02/2024	1270	35033	52737
Spark	481680	01/23/2025	1304	35441	53393
Spark	481763	01/23/2025	1304	35441	53393
Spark	482047	01/24/2025	890	32354	48254
Spark	482154	01/24/2025	1304	35450	53402
Radiance	460937	08/29/2024	65	618	4404
Radiance	461169	08/30/2024	45	232	4404
Radiance	461227	08/30/2024	12	618	1547
Radiance	461229	08/30/2024	65	200	1100
Vision Venture	476179	12/10/2024	78	977	1629
Vision Venture	479004	01/03/2025	84	1242	1629
Vision Venture	479692	01/08/2025	84	1246	1641

I have a projectName filter on my report, and it is set for single selection only. I need to show the latest date data rows only in three different KPIs.

See example below:

In my dataset, for some projects, I have more than one same date [duplicate dates for last rows] for the month. In this scenario, we should consider only the last rows.

For example:

• If I select the project "Sky," the result should be shown in the KPI against the latest date [01/16/2025]:

  • Total Test: 815

  • Covered Line: 6974

  • Total Line: 7826

• If I select the project "Spark," there are two entries for the last date [01/24/2025]. In this case, we should consider only the last rows, and the result should be shown in the KPI:

  • Total Test: 1304

  • Covered Line: 35450

  • Total Line: 53402

• If I select the project "Radiance," there are three entries for the last date [08/30/2024]. In this case, we should consider only the last rows, and the result should be shown in the KPI:

  • Total Test: 65

  • Covered Line: 200

  • Total Line: 1100

[Irwan · Accepted solution]

hello @AmitSaini 

please check if this accomodate your need.

create three new measures with following DAX.

Total Test = 
var _RunId = 
CALCULATE(
    MAX('Table'[RunId])
)
Return
CALCULATE(
    MAX('Table'[TotalTest]),
    'Table'[RunId]=_RunId
)

Total Lines = 
var _RunId = 
CALCULATE(
    MAX('Table'[RunId])
)
Return
CALCULATE(
    MAX('Table'[TotalLine]),
    'Table'[RunId]=_RunId
)

Covered Lines = 
var _RunId = 
CALCULATE(
    MAX('Table'[RunId])
)
Return
CALCULATE(
    MAX('Table'[CoveredLine]),
    'Table'[RunId]=_RunId
)

Hope this will help.

Thank you.

[AmitSaini]

Hi @Irwan 

Thank you for the solution. It works really well.