Max Value of Running Total per Year -> want to exclude current Year from Max

[Fromit87]

Hi!

I have a measure RT_MONTH calculating the running total of unit sales per fiscal month/year.

With a second measure MAX_RT, I identified the max value per month for the annual running totals.

MAX_RT = 
CALCULATE(
    MAXX(values(FY_CAL[FSCL_YR]),SALES[RT_MONTH]))

That works as intended, but it obviously includes data for the current fiscal year, which I need to avoid.

How do I adjust my MAX_RT measure to return the max value for the last 5 fiscal years, but excluding the current fiscal year? 

Example:

If I look at the example of January, the value of 18 as a result of MAX_RT (left table) comes from fiscal year 2021 (right table).

The current fiscal year is identified by (I cannot use time intelligence, as fiscal years, fiscal months deviate from calendar yrs/months):

VAR CurrentFY = LOOKUPVALUE(FY_CAL[FSCL_YR],FY_CAL[CAL_DT],TODAY())

FY_CAL is the date table with a 1:* relationship of column FY_CAL[CAL_DT] to SALES[DATE_SOLD] 

Any help is highly appreciated. Thanks!

[Fromit87]

@amitchandak Thank you for your reply!

The moment I add a filter to the MAX_RT measure, it returns no longer the maximum running totals per month, but only the maximum values per month (separately, not cumulative anymore).

MAX_RT =
CALCULATE(MAXX(values(FY_CAL[FSCL_YR]),SALES[RT_MONTH]),FILTER('FY_CAL','FY_CAL'[FSCL_YR]<2021))

[Fromit87 · Accepted solution]

I finally got it to work. Seems, that I mixed MAXX with CALCULATE. I have a column in my date table, that identifies previous fiscal years by -1,-2,-3 etc., the current fiscal year is 0. That way I filtered the table values of fiscal year to include everything but the current year. Then the measure resulted in the max value for previous years, excluding the current year.

From a performance perspective CALCUALTETABLE might not be ideal, but I don't know if there is an easier way with the same result.

MAX_RT =
MAXX (
    CALCULATETABLE (
        VALUES ( 'FY_CAL'[FSCL_YR] ),
        FILTER ( FY_CAL, FY_CAL[FSCL_YR_DIFF_CNT] < 0 )
    ),
    CALCULATE ( [RT_MONTH] )
)

[amitchandak]

@Fromit87 , you should be able to use datesytd on top or first formula, because it can end date of choice

example

MAX_RT =
CALCULATE(
MAXX(values(FY_CAL[FSCL_YR]),SALES[RT_MONTH]) , datesytd('Date'[Date], "3/31"))

or like 

This Year = CALCULATE(MAXX(values(FY_CAL[FSCL_YR]),SALES[RT_MONTH]) ,filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.