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

AmitSaini — Tue, 25 Feb 2025 14:05:15 GMT

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

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

Irwan — Thu, 27 Feb 2025 03:38:23 GMT

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.

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

AmitSaini — Wed, 05 Mar 2025 05:23:55 GMT

Hi Irwan

Thank you for the solution. It works really well.

topic Re: Need to getting latest date rows only against duplicate dates and unique dates in DAX Commands and Tips

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

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

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