topic Re: AVERAGEX Last three years average is adding up instead of calculating the average in DAX Commands and Tips

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

YesP — Mon, 16 Sep 2024 04:54:32 GMT

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)

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

DataNinja777 — Mon, 16 Sep 2024 05:07:25 GMT

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,

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

YesP — Mon, 16 Sep 2024 06:11:39 GMT

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,

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

DataNinja777 — Mon, 16 Sep 2024 07:37:37 GMT

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,

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

YesP — Mon, 16 Sep 2024 10:20:19 GMT

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,

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

DataNinja777 — Wed, 18 Sep 2024 15:36:39 GMT

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,

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

YesP — Thu, 19 Sep 2024 23:31:36 GMT

Thank you DataNinja777 this works great!

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

YesP — Fri, 20 Sep 2024 02:48:48 GMT

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,