Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Last date Selection

Hi, 

 

I have the below dataset.  

SiteDate CompletedDaysLastClean
0.39.09.4 - Room2/13/2020 21:551
0.39.09.4 - Room2/18/2020 22:045
0.39.09.4 - Room2/21/2020 22:083
1-31-62 -  W/R2/14/2020 18:414
1-31-62 -  W/R2/18/2020 19:504
1-31-62 -  W/R2/19/2020 19:561
1-31-62 -  W/R2/20/2020 17:261
1-31-62 -  W/R2/21/2020 18:421
1-31-69 - PTW/R2/11/2020 20:371
1-31-69 - PTW/R2/14/2020 18:163
1-31-69 - PTW/R2/18/2020 19:454
1-31-69 - PTW/R2/19/2020 16:461
1-31-69 - PTW/R2/20/2020 16:351
1-31-69 - PTW/R2/21/2020 16:551

 

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
 
SiteDate CompletedDaysLastClean
0.39.09.4 - Room2/21/2020 22:083
1-31-62 -  W/R2/21/2020 18:421
1-31-69 - PTW/R2/21/2020 16:551

 

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

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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