Average year over year excluding non-recurring values

[Anonymous]

Hi,

I am having trouble calculating an average measure or column for each year with only recurring values.

In more detail, I want to calculate the average wage for employees in 2019 but only those that worked here in 2018 and vice versa.

The following is a fictional and simplified dataset:

Employee	Month (dd-mm-yyyy)	Wage	combi
001	1-1-2018	€                8.000	20180101001
002	1-1-2018	€                4.000	20180101002
003	1-1-2018	€                4.000	20180101003
004	1-1-2018	€                3.800	20180101004
001	1-12-2018	€                8.400	20181201001
002	1-12-2018	€                4.300	20181201002
003	1-12-2018	€                4.200	20181201003
005	1-12-2018	€                2.800	20181201005
002	1-1-2019	€                4.300	20190101002
003	1-1-2019	€                4.300	20190101003
005	1-1-2019	€                2.800	20190101005
002	1-12-2019	€                5.000	20191201002
003	1-12-2019	€                4.850	20191201003
005	1-12-2019	€                2.900	20191201005
Current calculation
average 2018	€                4.938
average 2019	€                4.025
Increase	-18%
Desired calculation only includes 002 and 003
average 2018	€                4.125
average 2019	€                4.613
Increase	12%

There are many more employeesin the actual dataset.

Currently i have three measures:

2018 wage/fte =

CALCULATE(

AVERAGE(table[wage]);

FILTER(ALL('Dim Date');'Dim Date'[CalendarYear] = 2018))

2019 wage/fte =

CALCULATE(

AVERAGE(table[wage]);

FILTER(ALL('Dim Date');'Dim Date'[CalendarYear] = 2019))

Increase =

[2019 wage/fte] / [2018 wage/fte] -1

(I also tried the quick measures, yoy change and rolling average, but couldn't get the to work with any of the date fields.)

Any ideas on how to filter the averages on nondistinct values?

Thanks

Wouter

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If you create relationships as below (using "Both" direction)

Then create measures

Min year = CALCULATE(MIN('date'[year]),ALLEXCEPT('Table','Table'[Employee]))

Max year = CALCULATE(MAX('date'[year]),ALLEXCEPT('Table','Table'[Employee]))

average_2018 = CALCULATE(AVERAGE('Table'[Wage]),FILTER(ALL('Table'), YEAR('Table'[Month])=2018&&[Min year]=2018&&[Max year]=2019))

average_2019 = CALCULATE(AVERAGE('Table'[Wage]),FILTER(ALL('Table'), YEAR('Table'[Month])=2019&&[Min year]=2018&&[Max year]=2019))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you!

Took me a while to see what you did, but as i understand it, [Min year] for instance gives me the first year a employee was in the dataset?

[v-juanli-msft]

Hi @Anonymous 

Yes, if there are more dates in your dataset, but you only want 2018, 2019 years' data, please let me know so that i can modify my solution for you.

Best Regards
Maggie

[Anonymous]

Thanks,

I Managed to change your solution myself a bit, introducing max month as well.

Works fine!