Get the latest status per Entry but different dates

[Anonymous]

Hello PBI Community!

I am currently working on a data where an application has a milestone, status, and date, wherein each application have its own dates and milestone progress.

Example data:

Application Number	Milestone	Status	Status Date Tagged
App1	New Application	New Application	1-Jan-19 8:40
App1	Under Review	Under Review	1-Jan-19 8:45
App1	Under Review	Findings	1-Jan-19 9:15
App2	New Application	New Application	1-Jan-19 10:00
App2	Under Review	Under Review	2-Jan-19 7:54
App2	Passed Review	For booking	3-Jan-19 16:00
App2	Booked	Booked	3-Jan-19 18:23
App3	New Application	New Application	3-Jan-19 8:12
App4	New Application	New Application	4-Jan-19 9:15
App4	Under Review	Under Review	4-Jan-19 13:11

And I want the data in Power BI to be like this:

New Application	1
Under Review	2
-Findings	1
-Under Review	1
Booked	1

What functions should I use to read the latest entry regardless of Application Number? Because currently I'm having this one:

New Application	4
Under Review	4
-Findings	1
-Under Review	3
Passed Review	1
-For booking	1
Booked	1

which captures all entry.

Thank you in advance for your help!

[v-shex-msft]

Hi @Anonymous ,

You can create a matrix visual and use milestone, status as row, write a measure as value, then drill row fields to bottom level and it should display the result you wanted.

measure =
CALCULATE (
    COUNTROWS ( VALUES ( Table[Status] ) ),
    VALUES ( Table[Milestone] )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft,

I got all the numbers but I got an extra row and count which I shouldn't get (Passed Review - For booking).

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can add a additional conditions to exclude passed milestone status:

measure =
CALCULATE (
    COUNTROWS ( VALUES ( Table[Status] ) ),
    VALUES ( Table[Milestone] ),
    Table[Milestone] <> "Passed Review"
)

Regards,

Xiaoxin Sheng