Count latest occurences with filters

[samc_26]

Hi, I’m trying to create a measure to count the last time a certain location was visited on a report within the last 30 days but I can’t get it to show the correct number. I think I know why but I don’t know how to write the DAX. This is how my data looks:

Location	Date	Open?	Age of visit
A	01/03/25	Yes	11
B	23/02/25	No	17
C	01/01/25	Yes	70
D	18/01/25	Yes	53
A	26/02/25	Yes	14
B	10/03/25	Yes	2
C	16/02/25	No	24
D	10/01/25	Yes	61

So sometimes there are instances where there have been two visits in the last 30 days, I need the measure to only count the last occurrence for that location so for this data the result would be 3 because location D both has visits more than 30 days ago.

I also need it to take into account whether the location is open or not as unsuccessful visits can't be counted.

I’ve spent hours trying to work this out and have got close to the number but it’s never quite right! Any help would be much appreciated! Thank you for reading 😊

[bhanu_gautam · Accepted solution]

@samc_26 Create a calculated column to check if the visit is within the last 30 days:

IsRecentVisit =
IF (
'Table'[Date] >= TODAY() - 30,
TRUE(),
FALSE()
)

Create a measure to find the last visit date for each location within the last 30 days:

DAX
LastVisitDate =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
'Table',
'Table'[IsRecentVisit] = TRUE() && 'Table'[Open?] = "Yes"
)
)

Create a measure to count the locations with valid last visits:

DAX
CountLastVisits =
CALCULATE (
COUNTROWS (
SUMMARIZE (
'Table',
'Table'[Location],
"LastVisit", [LastVisitDate]
)
),
FILTER (
'Table',
'Table'[LastVisitDate] >= TODAY() - 30
)
)

[samc_26]

You're a genius, I wish I could write DAX that quickly! Thank you very much it works perfectly!