AVERAGEX Last three years average is adding up instead of calculating the average

[YesP]

Hi, I have the following DAX to calculate average of the three last year. I am using Averagex, but for some reason it is adding up instead of calculating the average. I do not want to dived between 3 because there are some expenses that only ocurred in one or two year. Could you help me? Thanks,

Last 3 Years Average =

 VAR __NumberofYears = 3

 VAR __CurrentVisibleDate = MAX ('Calendar'[Date])

 VAR __DatesTable =

 CalculateTable(

    VALUES('Calendar'[Date]),

    Datesinperiod('Calendar'[Date],__CurrentVisibleDate,-__NumberofYears,YEAR),

    'Calendar'[Month Number] = MONTH (__CurrentVisibleDate))

VAR __SumExpenses=  

Calculate(

    [Total Cost],

     __DatesTable)

Return

 CALCULATE(

    AVERAGEX(VALUES('Calendar'[Year]), [Total Cost]), __DatesTable)

[YesP]

Hi @DataNinja777  and Community,

I will appretiate your help with this issue. I have the following table with two columns Order Number and Lenght. I want to create a column called "Is it a Correct Order? " If a Order number starts with "T" , "C", "V" follow by 7 digits, and has a total lengh of 14, 17 ,20 characters the answer is Yes, otherwise is No.

Regards,

[DataNinja777]

Hi @YesP ,

Here’s an adjustment to your measure that ensures the correct averaging of available years (rather than adding them up):

Last 3 Years Average =
 VAR __NumberofYears = 3
 VAR __CurrentVisibleDate = MAX ('Calendar'[Date])
 VAR __DatesTable =
    CALCULATETABLE(
        VALUES('Calendar'[Date]),
        DATESINPERIOD('Calendar'[Date], __CurrentVisibleDate, -__NumberofYears, YEAR),
        'Calendar'[Month Number] = MONTH (__CurrentVisibleDate)
    )
 VAR __TotalYearsWithExpenses = 
    COUNTROWS(
        VALUES('Calendar'[Year])
    ) -- Count the distinct years that have expenses
 VAR __SumExpenses =  
    CALCULATE(
        [Total Cost],
        __DatesTable
    )
RETURN
    DIVIDE(
        __SumExpenses,
        __TotalYearsWithExpenses, 
        0 -- Default value if no years are found
    )

This should now correctly calculate the average by dividing the sum of expenses by the number of years where expenses occurred, rather than always dividing by 3. Let me know if this works as expected!

Best regards,

[YesP]

Hi DataNinja777,

Thank you for your quick answer. It is still adding up for year 2022,2023, and 2024

This is the result when there is expense cero in a year. It is dividing between 3

Best regards,

[DataNinja777]

Hi @YesP ,

You can alternatively use the following simplified method:

Last 3 Years Average = 
VAR __CurrentYear = MAX('Calendar'[Year]) -- Get the current year
VAR __LastThreeYears =
    FILTER(
        ALL('Calendar'),
        'Calendar'[Year] <= __CurrentYear &&
        'Calendar'[Year] > (__CurrentYear - 3) -- Filter the last 3 years including the current year
    )
VAR __TotalExpenses = 
    CALCULATE(
        SUM('Table'[Expense]), -- Sum expenses for the last 3 years
        __LastThreeYears
    )


    
RETURN
__TotalExpenses

Then identify the number of years including less than 3 years:

YearsWithExpenses = 
    COUNTROWS(
        FILTER(
            DISTINCT(ALL('Calendar'[Year])), -- Removes any filters on Year
            'Calendar'[Year] <= MAX('Calendar'[Year]) &&
            'Calendar'[Year] > (MAX('Calendar'[Year]) - 3) -- Only count the last 3 years
        )
    )

Then, finally, get the annualized average. 

Last 3 year average (annualized) = divide( [Last 3 Years Average],[YearsWithExpenses])

The output using a dummy data is as shown below:

I attach an example pbix file for your reference.

Best regards,

[YesP]

Hi @DataNinja777 ,

Thank you very much for your impresive help.

I'm trying to create a visualization that includes a Monthly current year column and a last 3 years average column. For example if I filter by 2024 I want to see 2024-01, 2024-02, 2024-03 etc with a current month amount column of $41,719, $31,219 , $68,909 and a last 3 years average column (average of 2022-01, 2023-01, 2024-01) and if I have no expenses in 2022-01 I want my average to be calculated by only 2 years (2024-01 , 2023-01). For that I created a Dax which is on my initial post lines above.

In the same way if I filter by year 2023 I want to see a current year column (with 2023 expenses) and a Last 3 years average column (Avg 2023,2022) my data source start in 2022.

Thank you,

[DataNinja777 · Accepted solution]

Hi @YesP ,

You can achieve the desired output by using the following measure as an example:

Last 3 Years Average (monthly) = 
VAR __CurrentYear = MAX('Calendar'[Year]) -- Get the current year from the visual filter
VAR __CurrentMonth = MAX('Calendar'[Month Number]) -- Get the current month from the visual filter
VAR __LastThreeYears = 
    FILTER(
        ALL('Calendar'), -- Ignore existing filters on the Calendar table
        'Calendar'[Year] <= __CurrentYear &&
        'Calendar'[Year] > (__CurrentYear - 3) &&
        'Calendar'[Month Number] = __CurrentMonth -- Ensure we are calculating for the same month
    )
VAR __TotalExpenses = 
    CALCULATE(
        SUM('Table'[Expense]), -- Sum expenses for the same month across the last 3 years
        __LastThreeYears
    )
VAR __YearsWithExpenses = 
    COUNTROWS(
        FILTER(
            DISTINCT(ALL('Calendar'[Year])), -- Get distinct years
            'Calendar'[Year] <= __CurrentYear &&
            'Calendar'[Year] > (__CurrentYear - 3) &&
            CALCULATE(SUM('Table'[Expense])) > 0 -- Only count years with actual expenses
        )
    )
RETURN
DIVIDE(__TotalExpenses, __YearsWithExpenses, 0) -- Divide total expenses by the number of years with expenses

The resulting output is displayed below:

I attach an example pbix file for your reference.

Best regards,

[YesP]

Thank you @DataNinja777 this works great!