Last date Selection

[Anonymous]

Hi, 

I have the below dataset.  

Site	Date Completed	DaysLastClean
0.39.09.4 - Room	2/13/2020 21:55	1
0.39.09.4 - Room	2/18/2020 22:04	5
0.39.09.4 - Room	2/21/2020 22:08	3
1-31-62 -  W/R	2/14/2020 18:41	4
1-31-62 -  W/R	2/18/2020 19:50	4
1-31-62 -  W/R	2/19/2020 19:56	1
1-31-62 -  W/R	2/20/2020 17:26	1
1-31-62 -  W/R	2/21/2020 18:42	1
1-31-69 - PTW/R	2/11/2020 20:37	1
1-31-69 - PTW/R	2/14/2020 18:16	3
1-31-69 - PTW/R	2/18/2020 19:45	4
1-31-69 - PTW/R	2/19/2020 16:46	1
1-31-69 - PTW/R	2/20/2020 16:35	1
1-31-69 - PTW/R	2/21/2020 16:55	1

The column "DaysLastClean" is a calculated columns 

DaysLastClean =

VAR temp =

TOPN (1,FILTER (Routine,Routine[Site] = EARLIER ( Routine[Site] )

&& Routine[Date Completed] < EARLIER ( Routine[Date Completed] )

&& Routine[Status] = "Complete"

),Routine[Date Completed], DESC)

RETURN

DATEDIFF ( MINX ( temp, Routine[Date Completed]), Routine[Date Completed], DAY )

Quetsion= What I need to show is rows for the latest date only.

End result of the above table

Site	Date Completed	DaysLastClean
0.39.09.4 - Room	2/21/2020 22:08	3
1-31-62 -  W/R	2/21/2020 18:42	1
1-31-69 - PTW/R	2/21/2020 16:55	1

Appreciate all help for a Measure or column to achieve the above result.

[Greg_Deckler]

I think you just need a table visualization with Site in it and Date set to an aggregation of Last and then your DaysLastClean?

[amitchandak · Accepted solution]

Refer

Measure = 
VAR __id = MAX ( 'Table'[Site] )
VAR __date = CALCULATE ( MAX( 'Table'[Date Completed] ), ALLSELECTED ( 'Table' ),  'Table'[Site] = __id ) 
RETURN CALCULATE ( max ( 'Table'[DaysLastClean] ), VALUES ( 'Table'[Site] ), 'Table'[Site] = __id, 'Table'[Date Completed] = __date )

Appreciate your Kudos.

[Anonymous]

@amitchandak - Thanks for the measure. The solution works perfectly.