topic Re: Return the Maximum count accordingfor past 3 years in DAX Commands and Tips

Return the Maximum count accordingfor past 3 years

Oelshamy — Wed, 05 Jun 2024 07:36:16 GMT

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

Re: Return the Maximum count accordingfor past 3 years

Joe_Barry — Wed, 05 Jun 2024 12:23:10 GMT

Hi Oelshamy

You could try. It seems to work

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

Joe

Re: Return the Maximum count accordingfor past 3 years

Oelshamy — Wed, 05 Jun 2024 12:48:18 GMT

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.

Re: Return the Maximum count accordingfor past 3 years

Joe_Barry — Wed, 05 Jun 2024 12:57:53 GMT

Sorry I misunderstood, glad you got it working!

Re: Return the Maximum count accordingfor past 3 years

Anonymous — Thu, 06 Jun 2024 05:01:02 GMT

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

Re: Return the Maximum count accordingfor past 3 years

Oelshamy — Mon, 10 Jun 2024 08:44:08 GMT

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]

)