Predicting the Future  Crimes Against Children in India (2001–2012) Using Power BI

---

Open Crimes Against Children Dataset

Part 1: Data Preparation in Power BI

Step 1: Load the Dataset

1. Open Power BI Desktop.

2. Go to Home → Get Data → Excel.

3. Browse to your file: Crimes_Against_Children_2001_2012.xlsx.

4. Select the relevant sheet.

---

🔄 Step 2: Transform the Data

The original structure: | State | Crime Head | 2001 | 2002 | ... | 2012 |

Steps:

1. Click Transform Data.

2. Select columns 2001 to 2012 → Right-click → Unpivot Columns.

3. Rename:

   • Attribute → Year

   • Value → Cases

4. Set data types:

   • Year → Whole Number

   • Cases → Whole Number

5. Click Close & Apply.

Final structure: | State | Crime Head | Year | Cases |

---

 Part 2: Visualize Trends

Trend Analysis Charts to Create in Power BI:

Chart 1: Year-wise Trend (Line Chart)

• Visual Type: Line Chart

• Axis: Year

• Values: Sum of Cases

• Legend (Optional): Crime Head

Chart 2: State-wise Crime Summary (Bar Chart)

• Visual Type: Bar Chart

• Axis: State

• Values: Sum of Cases

• Filters: Add slicer for Year or Crime Head

Chart 3: Crime Head Proportion (Donut/Bar Chart)

• Axis: Crime Head

• Values: Sum of Cases

---

 Part 5: Research Questions – Now with Detailed Steps

---

Trend Analysis Questions

---

Q1: What are the year-wise trends in crimes against children from 2001 to 2012?

➤ Steps:

1. Create a Line Chart:

   • X-axis: Year

   • Y-axis: Sum of Cases

2. Use slicer for Crime Head to see trends of each crime type individually.

---

Q2: Which states reported the highest number of such crimes?

➤ Steps:

1. Use a Stacked Column Chart or Bar Chart.

2. Axis: State

3. Values: Sum of Cases

4. Optional:

   • Add slicer for Year to view state-wise comparisons over time.

---

Q3: What are the most reported crime types?

➤ Steps:

1. Use a Donut Chart or Bar Chart.

2. Axis: Crime Head

3. Values: Sum of Cases

4. Optional:

   • Add slicer for State or Year to narrow the analysis.

---

 Forecasting & Predictive Research Questions

Now we move to forecasting in Power BI without Python, using its built-in Analytics features.

---

Q4: How will the trend of crimes against children evolve in the next 5 to 10 years?

➤ Steps:

1. Add a Line Chart:

   • X-axis: Year

   • Y-axis: Sum of Cases

2. Go to Analytics Pane (on the visual’s right)

3. Click + Forecast

4. Set:

   • Forecast length: 5 or 10 (years)

   • Confidence Interval: 95%

   • Seasonality: Auto

5. Click Apply

6. Result: Power BI draws dotted lines with a prediction band

---

Q5: Which crime heads are expected to rise more rapidly?

➤ Steps:

1. Use the same Line Chart as above

2. Add Legend → Crime Head

3. Add Forecast again via Analytics pane

4. Use Crime Head slicer to compare projected increase by type

---

Q6: Are there specific states projected to experience alarming increases?

➤ Steps:

1. Create a new Line Chart

   • X-axis: Year

   • Y-axis: Sum of Cases

   • Legend: State

2. Add Forecast via Analytics pane

3. Use slicers:

   • Filter Crime Head to view trends for specific crimes in each state

4. Analyze which states show steep upward trends in forecast lines

---

Q7: What is the expected annual growth rate of total crimes against children?

➤ Steps:

1. After applying forecast, observe:

   • Predicted values for each future year

2. Create a Table or Card to show:

   • Predicted value in 2012 (last known year)

   • Predicted value in 2022

3. Use a DAX Measure:

   CAGR = 
   (([Value in 2022] / [Value in 2012]) ^ (1 / 10)) - 1
   

   (If needed, replace hardcoded values with actual ones from your forecast table)

---

Q8: Can interventions be proposed based on forecasted hot-spots?

➤ Steps:

1. Create Map Visuals:

   • Use State as Location

   • Use forecasted Cases as Size/Color

2. Use filters for:

   • Top 5 states by forecasted increase

   • Top 5 crime heads

3. Show trend lines for these states in individual visuals

4. Based on charts, suggest policies:

   • State-specific interventions

   • Campaigns against specific crimes (e.g., sexual assault, kidnapping)

---

 Final Part: Dashboard 

🧩 Include:

• Year-wise line chart with forecast

• State and crime-wise breakdowns

• Top 5 predicted crime heads

• Geo-maps of forecasted hotspots

📌 Summary for readers:

• Which crimes are growing fast

• Where (states/cities) we need attention

• Forecasted numbers and policy suggestions

---