topic Re: Power BI: Showing latest 3 dates for a group in DAX Commands and Tips

Power BI: Showing latest 3 dates for a group

Anonymous — Thu, 24 Nov 2022 15:38:26 GMT

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

Re: Power BI: Showing latest 3 dates for a group

FreemanZ — Thu, 24 Nov 2022 14:57:11 GMT

try to create a new table with the code below:

Table =

VAR Table1 =

FILTER(

TableName,

TableName[Status] = "Completed"

)

VAR Table2=

TOPN(

Table1,

TableName[Date]

)

RETURN Table2

I tried and it worked like this:

Re: Power BI: Showing latest 3 dates for a group

Anonymous — Thu, 24 Nov 2022 15:34:36 GMT

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.

Re: Power BI: Showing latest 3 dates for a group

tamerj1 — Thu, 24 Nov 2022 16:07:54 GMT

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

Re: Power BI: Showing latest 3 dates for a group

CNENFRNL — Thu, 24 Nov 2022 20:18:23 GMT

A showcase of powerful Excel formula,

Re: Power BI: Showing latest 3 dates for a group

Anonymous — Wed, 07 Dec 2022 09:38:12 GMT

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,