How to show the latest date per ID within selected date range in Power BI

[GKJARC]

Hi everyone,

For a Power BI report I'm trying to show the latest ParticipationDate per ID in a table visual, within the selected ParticipationDate range. Here is some sample data:
ID    Partitipation date

1        02-03-2020

1        16-04-2020

1        02-12-2021

2        05-01-2020

2        08-09-2020

2        06-02-2021

The desired result, with the ParticipationDate range slicer set from 01-01-2020 to 31-12-2020:

ID    PartitipationDate    LatestParticipationDatePerID

1        02-03-2020                     16-04-2020

1        16-04-2020                     16-04-2020

2        05-01-2020                     08-09-2020

2        08-09-2020                     08-09-2020

I managed to show the latest date per ID with this calculated column:

LatestParticipationDatePerID =  CALCULATE ( MAX ( Table1[ParticipationDate] ), ALLEXCEPT (Table1, Table1[ID] ) )
This shows the latest participation date grouped per ID, but still regardless of the selected date range. I can't seem to get the DAX to work when I add an ALLSELECTED of ParticipationDate.

How could the desired result be achieved?

Any suggestions would be much appreciated..

Thanks!

[v-yalanwu-msft · Accepted solution]

Hi @GKJARC , 

You could create date table as a slicer. Then create a measure by the following formula:

First: create Date table and Use as a slicer

Date = CALENDAR(MIN('Table'[PartitipationDate1]),TODAY())

Then create a measure

Latest =
MAXX (
    FILTER (
        ALL ( 'Table' ),
        [ID] = MAX ( [ID] )
            && [PartitipationDate1] >= MIN ( 'Date'[Date] )
            && [PartitipationDate1] <= MAX ( 'Date'[Date] )
    ),
    [PartitipationDate1]
)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GKJARC]

Thank you very mucht @ v-yalanwu-msft for your reply. It is a solution to my initial request.

One follow-up question if I may. In this case the matching row should be marked as 1, the middle row in the example below.

My current calculated column 'Latest Y/N' ignores the selection Date[Date], is there a way to include this Date selection in the calculated column?

Latest Y/N = 
   IF ( [Latest participation date within selection] = Table1[ParticipationDate], 1, 0)

[amitchandak]

@GKJARC , Try like

CALCULATE ( MAX ( Table1[ParticipationDate] ), filter(allselected(Table1), Table1[ID] =max(Table1[ID]) ) )

[GKJARC]

Thanks @amitchandak for your reply. After creating this calculated column, for some reason it still seems to select the max ParticipationDate outside of the selected ParticipationDate range. The date range slicer is also Table1[ParticipationDate] so I don't understand why ALLSELECTED doesn't seem to work in this case.