DAX Query - Filtering by latest date/time with Direct Query Dataset

Hi There,
Have got a dataset which records the ANPR details of car movements, each time a car arrives on site it records which camera it arrived by and which direction it was going. There are 3 cameras, 1 which is only for exit, 1 only for entrance and 1 which can be either entrance or exit. I am looking to calculate the cars that are on site at any particular time. I have measures which show the count of car movements through each camera (and direction). But the data is stored in a SQL database so am using direct query and need to be able to see what the last time a particular car went through a camera and which one it was so I can work out which cars are on site, Ideally i would then like to be able to visualise this over time so we can the number of cars on site throughout the day and how that varies and also show which cars are on site at any time (which I can then analyse by make/,model/year of manufacture etc, have a got an API that can pull the appropriate details)

Have been experimenting with Calculate(CountAX(Car Reg)) but have struggled to think about the filters that pull the correct data.

Data Structure is
Access_date (Date Format)
AccessDateTime (Time Format)
Camera (3 Different Values)
CarReg (Car Registration that ANPR has captured)
Createdon (Date/Time Format)
Direction (Forward/Reverse/Unknown)

Measures already built
Farm Entrance = CALCULATE(COUNTA(anprData[CarReg]),FILTER(anprData,anprData[camera]=“FARMANPR”&&anprData[Direction]=“Forward”))
Entered Resort Barrier = CALCULATE(COUNTA(anprData[CarReg]),anprData[camera]=“ArrivalsEntryANPR”)
Farm Exit = CALCULATE(COUNTA(anprData[CarReg]),FILTER(anprData,anprData[camera]=“FARMANPR”&&anprData[Direction]=“Reverse”))
Left Resort Barrier = CALCULATE(COUNTA(anprData[CarReg]),anprData[camera]=“VisitorExit”)
In Out Movement = [Farm Entrance] + [Entered Resort Barrier] - [Left Resort Barrier] - [Farm Exit]

Any help would be greatly appreciated


Although it would be helpful to have a sample pbix a general response. You can find many solutions for events happening between times, such as HR related and Patients in hospitals. BrianJ did a nice one recently on HR, link to the topic on the forum, Create new dynamis table using selected value from slicer

To the contributors of this post, thank you for all your inputs on this topic we are now tagging it as Solved. To help us learn more about your experience in the forum, please take a moment to answer this short forum survey. We appreciate all your help and suggestions. Thanks!