cancel
Showing results for
Did you mean:
Highlighted
New Member

## Crime Analysis Table

I am trying to do a real time crime analysis table visual based off of a live sql query. I have a data table which has columns that provides the date (date_occurred) the crime took place, the uniform crime report (ucr) number and  the description (UCR_description) for each UCR code.

 Date Occurred UCR Code Code Description 1/1/20 110 H 1/1/20 210 S 1/1/20 740 AU 1/1/20 600 B 1/1/20 600 B 1/1/20 310 R 1/1/20 310 R 1/1/20 310 R 1/1/20 210 S 12/12/19 820 T 12/12/19 820 T 12/12/19 810 T 12/12/19 740 AU 12/12/19 900 AR 12/12/19 110 H 12/12/19 210 S 12/12/19 210 S 12/12/19 600 B 1/17/20 600 B 1/17/20 310 R 1/17/20 310 R 1/17/20 310 R 1/17/20 210 S 1/17/20 820 T 1/17/20 820 T 1/17/20 810 T 1/17/20 740 AU 1/17/20 900 AR 1/17/20 900 AR

I am trying to do a year to date comparision by each UCR code that would produce a table visual like this:

 UCR Description 2020 2019 % Change 110 H 1 1 0.00% 210 S 1 2 -50.00% 310 R 6 0 600.00% 600 B 3 1 200.00% 740 AU 2 1 100.00% 810 T 1 1 0.00% 820 T 2 2 0.00% 900 AR 2 1 100.00%

I can get the table visual to work for YTD 2020 but can not figure out how to show the year to date comparision between 2020 and 2019. The year to date would always compair this year to last year.

Any help would be appreciated.

2 REPLIES 2
Highlighted
Super User IV

## Re: Crime Analysis Table

@CliffordHagan there are lots of posts on comparing with Previous Year and the key is to add date/calendar dimension in your model. There are many posts on how you add date/calendar dimension and as it is a best practice to do all Time Intelligence calculations from this date/calendar dimension. This is your starting point and you will easily get it working using time intelligence functions.

I would 💖 Kudos 🙂 if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Super User IV

## Re: Crime Analysis Table

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.