Most recent of each within filtered data

[sjp532]

Hi all,

I'm having a lot of trouble trying to write a DAX query for the following data set:

I've trying to create measures to count the distinct number of SiteIDs scoring "DE", "UC", etc on their most recent visit within the filtered data. I've added in the calculated column MaxDate, which seems to successfuly show the date of the most recent visit ever to a site.

MaxDate = CALCULATE(MAX(Visits[Date]), FILTER(Visits, EARLIER(Visits[SiteID])=Visits[SiteID]))

However, if I filter to periods 1&2 then site id 203 shows the MaxDate as the max visit date ever ( which is in period 3), not the visit date in period 2 as I'd like:

Is there a way I write this to calculate the max date within the filtered data only?

Any help would be much appreciated as I've spent a long time trying to figure this out!

Many thanks,

Stephen

[v-yuezhe-msft · Accepted solution]

@sjp532,

Calculate columns are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report. Please create a measure instead and create visual as shown in the following screenshot.

Measure  = MAX(Visits[Date])

Reference:
https://community.powerbi.com/t5/Community-Knowledge-Base/Calculated-Column-Table-Change-Dynamically...

Regards,
Lydia