Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi,
You may download my PBI file from here.
Hope this helps.
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |