Help required to write a DAX measure

[Cevola]

Hello,

I need some help in the model and writing a measure. I need to write 2 measures to calculate the number of accident ids for each camera based on camera installation date i.e number of accident ids for 3 months before and 3 months after the camera installation date.

Camera table and Accident table are both fact tables.

Model:

Camera table- Camera ID and Date columns

Cam Detailts- Camera ID, Camera Installation Date, Road name and other details

Metadata- Road name and other details

Accident ODDs- Accident short name and Road name

Accidents- Date, Accident short name, Accident ID

Calendar- Dimension table

Relationships: 

Camera table and Cam details - linked on Camera ID

Cam details and Metadata- linked on Road name

Metadata and Accident ODDs - linked on Road name

Accident ODDs and Accidents- linked on Accident short name

Accidents and Calendar- linked on date field

Calendar and Camera table - linked on date field

When i write the measure I am not able write the filter condition to check the dates. Please help.

[technolog]

Alright, let's break this down. You want to calculate the number of accident IDs for each camera based on the camera installation date. Specifically, you want to count the number of accidents that occurred 3 months before and 3 months after the camera installation date.

First, we need to get the installation date for each camera. Since the Cam Details table has the Camera Installation Date, and it's linked to the Camera table through Camera ID, we can use the RELATED function to get the installation date for each camera.

Now, for the measure to count the accidents 3 months before and after the installation date, we'll use the FILTER function to filter the Accidents table based on the date range we want. The DATEADD function will be useful here to calculate the date range.

Here's how you can write the measure:

Accidents Around Installation :=
VAR InstallationDate = RELATED('Cam Details'[Camera Installation Date])
VAR StartDate = DATEADD(InstallationDate, -3, MONTH)
VAR EndDate = DATEADD(InstallationDate, 3, MONTH)
RETURN
CALCULATE(
COUNT('Accidents'[Accident ID]),
FILTER(
'Accidents',
'Accidents'[Date] >= StartDate && 'Accidents'[Date] <= EndDate
)
)
This measure first determines the installation date for the current context's camera. It then calculates the start and end dates for our 3-month window. Finally, it counts the number of accidents that fall within this date range.

You can add this measure to a visual or table that has the Camera ID in context, and it should give you the count of accidents for each camera based on its installation date.