Avg Expenses by month not updating with slider

[Gonaaa]

Ciao,

i'm trying to have a monthly average of the expenses that not updates with the current filter /slider.

I was successfull in excluding the measure from the filters and keep the data related to the period i want, but unfortunately i only have the Year average and not the monthly average.

here is the formula

Media Spese 2021-22 =
CALCULATE(AVERAGEX(GROUPBY(TotaleMovimenti,'Calendar'[Date].[Year],'Calendar'[Date].[Month]),TotaleMovimenti[Totale]), FILTER(ALLEXCEPT('Calendar','Calendar'[Date].[Month]),'Calendar'[Date].[Year]>2020 && 'Calendar'[Date].[Year]<2023), ALL('Calendar'[Date]))

What should i change?

I have 4 different measure:

Totale --> is the SUM of the expenses per month in a selected period (e.g. 2021-2022)

SpesaMediaMese --> is the total AVERAGE per months of all the data available in the database (eg. 2006-2023), does not update with any filter/slider

MediaSpese2 --> is the AVERAGE of the expenses per month in the selected period (e.g. 2021-2022) and updates with the filter/slider

MediaSpese 2021-22 --> should be AVERAGE of the expenses per month in the selected period (e.g. last 2 full years) and  should not updates with the filter/slider.

In the case above, Mediaspese2  and Media Spese 2021-2022 should be identical, given i selected the same date period

Thank you

[Gonaaa]

UP!

pls

🙂

[Anonymous · Accepted solution]

Hi @Gonaaa ,

I think you can try code as below to create a measure.

MediaSpese 2021-22 = 
VAR _RESULT1 =
    CALCULATE (
        [MediaSpese2],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Month] = MAX ( 'Calendar'[Month] )
                && 'Calendar'[Year] >= 2021
                && 'Calendar'[Year] <= 2022
        )
    )
VAR _RESULT2 =
    CALCULATE (
        [MediaSpese2],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Year] >= 2021
                && 'Calendar'[Year] <= 2022
        )
    )
RETURN
    IF ( HASONEVALUE ( 'Calendar'[Month] ), _RESULT1, _RESULT2 )

Result is as below.

You can download my sample file to learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gonaaa]

Brilliant!

thank you very much