Forum Discussion

Oelshamy's avatar
Oelshamy
Icon for Helper I rankHelper I
2 years ago
Solved

Return the Maximum count accordingfor past 3 years

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 

"Time Intelligence"
DAX
DAX compare similar past period
  • Oelshamy's avatar
    Oelshamy
    2 years ago

    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]
    )
     

     



     

5 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi Oelshamy 

     

    You could try. It seems to work

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

     Joe

     

    • Oelshamy's avatar
      Oelshamy
      Icon for Helper I rankHelper I
      2 years ago

      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. 

       

       

      • Joe_Barry's avatar
        Joe_Barry
        Icon for Solution Sage rankSolution Sage
        2 years ago

        Sorry I misunderstood, glad you got it working!

  • Oelshamy's avatar
    Oelshamy
    Icon for Helper I rankHelper I
    2 years ago

    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]
    )