Average of distinct month across several years

[h4tt3n]

Hi,

I am doing an energy consumption report with a column chart that shows consumption by month along with an average of the last few years of consumption, for comparison. So, for clarity, when showing energy consumption for january 2022, I want to also show a bar displaying the average consumption for january 2019-2021 (alternatively for all january months with data).

The tables in question are a data table with value and timestamp, and a calendar datetime table. The two are connected with a one-to-many relation.

How would I do this in DAX?

When searching for this problem, several solutions come up, but I haven't been able to find one that does the trick for me.

This solution is a close match, but the challenge seems to be that I am using year / month in my x axis, so it will not display present and historic data on the same place along the time axis. IT shows nothing or the same value as this months consumption.

This and this solution also seems close, but I cannot make them return meaningful data.

Cheers, Mike

[SpartaBI · Accepted solution]

If you don't have that column you can also do something like that (but again, highlly recommend you always have that column - and also a numeric represntations of that column to sort it by):

Avg Consumption Previous Years = 

VAR _currentyear = MAX('CalendarDateTime'[Year])
VAR _currentmonth = MAX('CalendarDateTime'[MonthOfYear])
VAR _result = 
CALCULATE(
	AVERAGEX(
		SUMMARIZE(
			'CalendarDateTime',
			'CalendarDateTime'[Year],
			'CalendarDateTime'[MonthOfYear]
		),
		[Sum of Consumption]
	),
	REMOVEFILTERS('CalendarDateTime'),
	'CalendarDateTime'[MonthOfYear] = _currentmonth,
	'CalendarDateTime'[Year] < _currentyear
)
RETURN
	_result

[SpartaBI]

Let's say your primary measure is called [Consumption] and it shows the value for the current filter context, so for Jan 2022 it will show the result for Jan 2022.
The other measure you are looking for is:

[Avg Consumption Previous Years] = 

VAR _currentyear = MAX('Date'[Year])
VAR _currentmonth = MAX('Date'[MonthNo])
VAR _result = 
CALCULATE(
	AVERAGEX(
		VALUES('Date'[Year-Month]),
		[Consumtion]
	),
	REMOVEFILTERS('Date'),
	'Date'[MonthNo] = _currentmonth
	'Date'[Year] < _currentyear
)
RETURN
	_result

[h4tt3n]

Hi @SpartaBI,

Thanks for your reply. I am struggling to figure out what you mean by

'Date'[Year-Month],

Could you please elaborate on what this contains? By the way, I was unable to assign any column argument to the Averagex function, it would only accept a table argument.

This is my translation of your code, so far:

Avg Consumption Previous Years = 

VAR _currentyear = MAX('CalendarDateTime'[Year])
VAR _currentmonth = MAX('CalendarDateTime'[MonthOfYear])
VAR _result = 
CALCULATE(
	AVERAGEX(
		'CalendarDateTime',
		[Sum of Consumption]
	),
	REMOVEFILTERS('CalendarDateTime'),
	'CalendarDateTime'[MonthOfYear] = _currentmonth,
	'CalendarDateTime'[Year] < _currentyear
)
RETURN
	_result

Cheers, Mike

[SpartaBI]

Hey, @h4tt3n,
Sorry, had a typo, I fixed it. Wrapped that column with VALUES(..)
The column [Year-Month] is a column that represnet a specific month in a specific year, like Jan 2021 etc. Good to always have this column in your date table

[h4tt3n]

Okay, like eg. january 2020 would then be 01-01-2020 and so on, with the date always set to 01? Or like this: 

Year-Month = FORMAT('Table'[Date], "YYYY-MM")

I got your DAX equation returning plausible data - now I'll have to do some testing to see if I got it right.

[SpartaBI]

@h4tt3n 
Both versions will work fine 🙂 
Better to use the YYYY-MM for usability, so it easy to understand you mean year-month and not a date when someone looks at it.
Please don't forget to mark this as a solution in case it solved your question

[h4tt3n]

@SpartaBI 

I ended up using this solution, based on your answer.

AvgConsumptionPreviousYears = 

VAR _numPrevYears = 2
VAR _currentyear = MAX('CalendarDateTime'[Year])
VAR _currentmonth = MAX('CalendarDateTime'[MonthOfYear])
VAR _result = 
CALCULATE(
	AVERAGEX(
		SUMMARIZE(
			'CalendarDateTime',
			'CalendarDateTime'[Year],
			'CalendarDateTime'[MonthOfYear]
		),
		[Sum of Consumption]
	),
	REMOVEFILTERS('CalendarDateTime'),
    	'CalendarDateTime'[MonthOfYear] = _currentmonth,
       'CalendarDateTime'[Year] >= _currentyear - _numPrevYears,
       'CalendarDateTime'[Year] < _currentyear
)
RETURN
	_result

 Thanks for helping me out.

Cheers, Mike