Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
This is my first post, so I hope I am in the right place. lol Any help will be greatly appreciated!
I am new to using PowerBI and I have a report that displays the total number of Fatal, Injury, and Non-Injury collisions (DIType), by month (Date). But I need to be able to compare that result to the average number for each type, based on the previous 5 year average for the same month. (FYI, I created a date-table which is linked to my dates in the data source.) I am stumped on how to create a measure that will look at the "current" month (ie June) based on my date-picker, and then look at each of the same month for the previous 5 years (ie June), and give me an average total of each collision for that same month.
I.E.
Jun-18 | 5yr Avg-June | |
Fatal | 0 | 0 |
Inury | 28 | 26 |
Non-Injury | 67 | 58 |
Sample of what my data source looks like:
Date | AccidentNumber | DIType | Location | Lon | Lat | PrivProp |
10/31/2015 | 2015-00030213 | NON-INJURY | HARRIER HAWK / REDTAIL HAWK | -97.49294942 | 35.6722583 | N |
11/6/2015 | 2015-00031483 | INJURY | BAVARIAN CT / LEAWOOD DR | -97.42840006 | 35.66562862 | N |
8/31/2015 | 2015-00019486 | NON-INJURY | MONARCHOS DR / WINNING COLORS DR | -97.4935245 | 35.70145609 | N |
10/25/2017 | 2017-00064355 | NON-INJURY | W DANFORTH RD / N BROADWAY | -97.48067621 | 35.66733337 | N |
10/12/2017 | 2017-00061840 | NON-INJURY | W DANFORTH RD / N BROADWAY | -97.48067621 | 35.66733337 | N |
6/30/2017 | 2017-00039299 | NON-INJURY | W DANFORTH RD / N BROADWAY | -97.48067621 | 35.66733337 | N |
5/6/2017 | 2017-00027659 | NON-INJURY | W DANFORTH RD / N BROADWAY | -97.48067621 | 35.66733337 | N |
3/2/2017 | 2017-00013702 | NON-INJURY | W DANFORTH RD / N BROADWAY | -97.48067621 | 35.66733337 | N |
9/23/2016 | 2016-00065677 | NON-INJURY | W DANFORTH RD / N BROADWAY | -97.48067621 | 35.66733337 | N |
5/19/2016 | 2016-00033857 | NON-INJURY | W DANFORTH RD / N BROADWAY | -97.48067621 | 35.66733337 | N |
4/25/2016 | 2016-00027973 | NON-INJURY | W DANFORTH RD / N BROADWAY | -97.48067621 | 35.66733337 | N |
4/5/2018 | 2018-00023924 | NON-INJURY | E 2ND ST / S BROADWAY | -97.48151969 | 35.65319069 | N |
3/30/2018 | 2018-00022401 | NON-INJURY | E 2ND ST / S BROADWAY | -97.48151969 | 35.65319069 | N |
2/13/2018 | 2018-00010637 | NON-INJURY | E 2ND ST / S BROADWAY | -97.48151969 | 35.65319069 | N |
2/10/2018 | 2018-00009873 | INJURY | E 2ND ST / S BROADWAY | -97.48151969 | 35.65319069 | N |
Thanks in advance!
Bruce L.
Hi Bruce,
Try the formulas below and the demo in the attachment please.
Jun-18 = CALCULATE ( COUNT ( Table1[DIType] ), MONTH ( Table1[Date] ) = MONTH ( TODAY () ) )
5yr Avg-June = CALCULATE ( AVERAGEX ( SUMMARIZE ( 'Table1', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month], "CountAmount", COUNT ( Table1[DIType] ) ), [CountAmount] ), DATESINPERIOD ( 'Calendar'[Date], TODAY (), -5, YEAR ), MONTH ( 'Calendar'[Date] ) = MONTH ( TODAY () ) )
Best Regards,
Dale
Hi Dale, thank you for the help! I am still trying to get it work, however, I'm struggling. 😞
I will post some screens in hopes that it might show what my expected result will look like.
more to follow
Thanks again!
Bruce
Hi Bruce,
Please share the file if possible. Mask the private data first.
Best Regards,
Dale
Here is a link to the file...hopefully it works correctly.
https://1drv.ms/u/s!AsaN6cHxfjiAmlWLcWDjWmP4I6NO
The picture is my process for what I am trying to accomplish, which is, using a date-picker to select a month and year for the "current" or focus, and then have an average of the same month selected, averaged across the previous 5 year period.
https://1drv.ms/u/s!AsaN6cHxfjiAmlbppA42pN8Jo5v2
Hi @bleehan,
Please check out the demo in the attachment which is based on your data.
1. Create a date table,
2. Dont' establish relationships with other tables.
3. Create a measure.
Measure = IF ( HASONEVALUE ( 'Calendar'[Date].[Year] ), IF ( MIN ( VCOE_PD_Accidents_All[Year] ) = SELECTEDVALUE ( 'Calendar'[Date].[Year] ), CALCULATE ( AVERAGEX ( SUMMARIZE ( VCOE_PD_Accidents_All, VCOE_PD_Accidents_All[Year], VCOE_PD_Accidents_All[Month], "countAmount", COUNT ( VCOE_PD_Accidents_All[DIType] ) ), [countAmount] ), FILTER ( ALL ( VCOE_PD_Accidents_All[Year] ), VCOE_PD_Accidents_All[Year] >= MIN ( VCOE_PD_Accidents_All[Year] ) - 5 && VCOE_PD_Accidents_All[Year] <= MIN ( VCOE_PD_Accidents_All[Year] ) ) ), COUNT ( VCOE_PD_Accidents_All[DIType] ) ), COUNT ( VCOE_PD_Accidents_All[DIType] ) )
Note: selecting other years other than the last year would be misleading. Because the average values will be in the middle.
Best Regards,
Dale
Dale, I apologize for wasting your time, that was the wrong report, that got attached. I am still unable to get this working, even on the correct report, which is here: https://1drv.ms/u/s!AsaN6cHxfjiAmlera0pTJNfYVVsl
Like I said before, I am new to PowerBI and DAX. I will continue to work on this.
Bruce L.
Good morning Dale, how do I attach the file to a forum post? Sorry, thanks
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |