Power BI: Showing latest 3 dates for a group

[Anonymous]

Hello everyone, 

I have a problem. I want to show the latest 3 dates only for status "Completed" as illustrated bellow. Tried rankx, topN value and max values approaches, but didn't work out. Basically I need to return the 3 most recent dates just for "Completed" along with any number of results for "Overdue" and "Future" (e.g. regardless of the number of their dates for the latter). A measure must be generated. Any solutions will be highly appreciated.

Thanks, KGeorge

[CNENFRNL · Accepted solution]

A showcase of powerful Excel formula,

[Anonymous]

Hello , thank you for you solution. As I specified the main goal is to have a measure with the latest 3 dates for completed; no filter / no ranks ( having anydates) for overdo and future status in one cumulative table. However, I believe your proposition is approaching the final solution of the issue.

Regards,

K.

[tamerj1]

Hi @Anonymous 

please try

FilterMeasure =
VAR CurrentDate =
SELECTEDVALUE ( 'Table'[Date] )
VAR CurrentStatus =
SELECTEDVALUE ( 'Table'[Status] )
VAR T1 =
CALCULATETABLE ( VALUES ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR T2 =
TOPN ( 3, T1, 'Table'[Date], ASC )
RETURN
IF ( CurrentStatus = "Completed", IF ( CurrentDate IN T2, 1 ), 1 )

place this measure in the filter pane of the visual and select "is not blank" then spply the filter

[FreemanZ]

try to create a new table with the code below:

Table =

VAR Table1 =

    FILTER(

        TableName,

        TableName[Status] = "Completed"

    )

VAR Table2=

TOPN(

    3,

    Table1,

    TableName[Date]

)

RETURN Table2

I tried and it worked like this:

[Anonymous]

Thank you very much for the response, I haven't specified that I need a measure , where the top 3 "Completed" will be shown along with the other status "Overdue","Future", regardless of both numbers of results.