Return the Maximum count accordingfor past 3 years

[Oelshamy]

I have a table called Safety Observations and counting the Safety Observations serial number and trying to return the maximum in each month for the past two years compare it with the current Year and add it in a trend line, as per the below image. 

For example, in January looking at the past 2 years - the maximum is in 2023 so it should return 30, meanwhile in February the maximum was in 2022 so I want to return 19. 

This is the Formula that i have used but its returning the 2024 .

Max Per Year = CALCULATE (COUNT('Safety Observations'[SerialNo]) ,
FILTER ( ALL ( 'Date Dimension' ), 'Date Dimension'[Month] = MAX ( 'Date Dimension'[Month] ) ),
DATESINPERIOD ( 'Date Dimension'[Date].[Date], LAST DATE ( 'Date Dimension'[Date].[Date] ), -2, YEAR )
)

Appreciate your guidance 

[Oelshamy · Accepted solution]

I have found the solution for this as below: 

Created a Measure for getting last years: 

_m_MaxLastTwoYears =

CALCULATE(

    MAXX(

        SUMMARIZE(

            FILTER(

                'Safety Observations',

                'Safety Observations'[Created].[Year] IN {YEAR(TODAY()) - 1, YEAR(TODAY()) - 2}

            ),

             'Date Dimension'[MonthYear],

            "MonthlyMax", Count('Safety Observations'[SerialNo])

        ),

        [MonthlyMax]

    )

)

then created a measure to get them for this year: 

_mMaxCurrentYear =

CALCULATE(

    MAXX(

        SUMMARIZE(

            FILTER(

                'Safety Observations',

                'Safety Observations'[Year] = YEAR(TODAY())

            ),

           'Safety Observations'[Created_Month],

            "MonthlyCount", COUNT('Safety Observations'[SerialNo])

        ),

        [MonthlyCount]

    )

)

Then created a measure that compares both and returns the best-performing month: 

_m_CompareMaxObservations =

IF(

    [_mMaxCurrentYear] > [_m_MaxLastTwoYears],

    [_mMaxCurrentYear],

    [_m_MaxLastTwoYears]

)

[Joe_Barry]

Hi @Oelshamy 

You could try. It seems to work

Trend line = CALCULATE(MAX(Inc[Incidents]), ALLEXCEPT('Date', 'Date'[Month Name]))

 Joe

[Oelshamy]

Dear Joe, Appreciate your response, I am not sure about your formuala but mine worked as below, i wanted to compare this year with the best performing month in 22 and 23 as below. 

[v-linyulu-msft]

Hi,@Oelshamy

We are happy to know that the issue has been resolved. We would appreciate it if you would be willing to share the solution with us to facilitate a solution for the same problem. Additionally, this would be beneficial to other community members as well.

Certainly, if there is anything else we can do for you, please do not hesitate to contact us.

Looking forward to your reply.

Best Regards,

Leroy Lu

[Joe_Barry]

Sorry I misunderstood, glad you got it working!