cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

current month vs average for current month over prev 5 years

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-185yr Avg-June
Fatal00
Inury2826
Non-Injury6758

 

Sample of what my data source looks like:

DateAccidentNumberDITypeLocationLonLatPrivProp
10/31/20152015-00030213NON-INJURYHARRIER HAWK / REDTAIL HAWK-97.4929494235.6722583N
11/6/20152015-00031483INJURYBAVARIAN CT / LEAWOOD DR-97.4284000635.66562862N
8/31/20152015-00019486NON-INJURYMONARCHOS DR / WINNING COLORS DR-97.493524535.70145609N
10/25/20172017-00064355NON-INJURYW DANFORTH RD / N   BROADWAY-97.4806762135.66733337N
10/12/20172017-00061840NON-INJURYW DANFORTH RD / N   BROADWAY-97.4806762135.66733337N
6/30/20172017-00039299NON-INJURYW DANFORTH RD / N   BROADWAY-97.4806762135.66733337N
5/6/20172017-00027659NON-INJURYW DANFORTH RD / N   BROADWAY-97.4806762135.66733337N
3/2/20172017-00013702NON-INJURYW DANFORTH RD / N   BROADWAY-97.4806762135.66733337N
9/23/20162016-00065677NON-INJURYW DANFORTH RD / N   BROADWAY-97.4806762135.66733337N
5/19/20162016-00033857NON-INJURYW DANFORTH RD / N   BROADWAY-97.4806762135.66733337N
4/25/20162016-00027973NON-INJURYW DANFORTH RD / N   BROADWAY-97.4806762135.66733337N
4/5/20182018-00023924NON-INJURYE 2ND ST / S   BROADWAY-97.4815196935.65319069N
3/30/20182018-00022401NON-INJURYE 2ND ST / S   BROADWAY-97.4815196935.65319069N
2/13/20182018-00010637NON-INJURYE 2ND ST / S   BROADWAY-97.4815196935.65319069N
2/10/20182018-00009873INJURYE 2ND ST / S   BROADWAY-97.4815196935.65319069N

 

Thanks in advance!

 

Bruce L.

7 REPLIES 7
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

current_month_vs_average_for_current_month_over_prev_5_year

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors