Road Accident | Power BI

Clients wants to create a Road Accident Dashboard for year 2021 and 2022 so that they can have insight on the below requirements :

• Primary KPI - Total Casualties and Total Accident values for Current Year and YoY growth
• Primary KPI's — Total Casualties by Accident Severity for Current Year and YoY growth
• Secondary KPI's - Total Casualties with respect to vehicle type for Current Year
• Monthly trend showing comparison of casualties for Current Year and Previous Year
• Casualties by Road Type for Current year
• Current Year Casualties by Area/ Location & by Day/ Night
• Total Casualties and Total Accidents by Location

DAX Measures --->

Total Accident = COUNT(Data[Accident_Index])
------------------------------------------------------------------------------
Total Casualties = SUM(Data[Number_of_Casualties])
------------------------------------------------------------------------------
CY Accident Count = TOTALYTD(COUNT(Data[Accident_Index]),'Calendar'[Date])
------------------------------------------------------------------------------
CY Casualties = TOTALYTD(SUM(Data[Number_of_Casualties]),'Calendar'[Date])
------------------------------------------------------------------------------
PY Acident Count = CALCULATE(COUNT(Data[Accident_Index]),SAMEPERIODLASTYEAR('Calendar'[Date]))
------------------------------------------------------------------------------
PY Casualties = CALCULATE(SUM(Data[Number_of_Casualties]),SAMEPERIODLASTYEAR('Calendar'[Date]))
------------------------------------------------------------------------------
Serious Casualties = CALCULATE([Total Casualties], FILTER(Data,Data[Accident_Severity]="Serious"))
------------------------------------------------------------------------------
YOY Accident = ([CY Accident Count]-[PY Aciident Count])/[PY Aciident Count]
------------------------------------------------------------------------------
YOY Casualties = ( [CY Casualties]-[PY Casualties])/[PY Casualties]

SQL Queries --->