Measure to calculate running maximum value per quarter

[QQ]

Your help is much appreciated!

I am trying to generate a measure: for each quarter, calculate the maximum value for all quarters before. Here is the table I want to generate on the report:

Quarter	Cases	Max Quarterly Cases -current	Max Quarterly Cases -Ideal
2010-Q1	110	110	110
2010-Q2	90	90	110
2010-Q3	80	80	110
2010-Q4	120	120	120
2011-Q1	130	130	130
2011-Q2	70	70	130
2011-Q3	150	150	150
2011-Q4	140	140	150
TOTAL	890	150	150

The quarters are from calendar table. Both [Cases] and [Max Quarterly Cases] are measures in case table. The cases are recorded by dates in case table and the dates are linked to calendar table. I used [Max Quarterly Cases] = maxx(values(Calendar[Fiscal_Year_Quarter]),[Cases]) and its result shows in Max Quarterly Cases -current. But what I need is the running maximum value as shown in Max Quarterly Cases - Ideal.

See some examples online but for calculated column, not for measure. Is there a way to do it using a measure?

Thanks a lot!

[v-yulgu-msft]

Hi @QQ,

Please try below measure formula:

Max Quarterly Cases =
MAXX (
    FILTER (
        ALLSELECTED ( 'Calendar'[Fiscal_Year_Quarter] ),
        'Calendar'[Fiscal_Year_Quarter] <= MAX ( 'Calendar'[Fiscal_Year_Quarter] )
    ),
    [Cases]
)

Best regards,
Yuliana Gu

[QQ · Accepted solution]

Thank you very much Yuliana! Sorry forgot to mention the [Fiscal_year_quarter] column is in text! So Calendar[Fiscal_year_quarter]<=max(Calendar[Fiscal_year_quarter]) does not work.

Based on your formula, I used Calculate function and now it is working! Here is the one:

Calculate(Maxx(values(Calendar[Fiscal_Year_Quarter]),[Cases]),Filter(all(Calendar),calendar[date]<=Max(Calendar[Date])))

Thanks a lot!