cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Calculate average of data at different levels by year

Hi 

I have the following Data :

 

CompanyYearGoodwillRevenue
Company A20187735191
CompanyB20187858675
Company A20175435352
CompanyB20172878178
Company A20168936210
CompanyB20165147959
Company A20153067690
CompanyB20155667360

 

I want to achieve a solution where, when I filter year it returns me the average of Goodwill of that year and the previous year. 

So for my year filter 2017: Average of Goodwill in 2017,2016 

                year filter 2016: Average of Goodwill in 2016,2015

                .... and so on

 

The year is in General format (NOT Date format) .. 

 

 

Expected OUTPUT Values: 

 

Company A2018658
CompanyB2018536
Company A2017718
CompanyB2017400.5
Company A2016599.5
CompanyB2016540
Company A2015306
CompanyB2015566
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

@charchitjoshi 

 

Try this measure

 

Measure = VAR myyear=SELECTEDVALUE(Table1[Year])
RETURN
CALCULATE(average(Table1[Goodwill]),Table1[Year] in {myyear,myyear-1})
Regards,
Zubair


View solution in original post

1 REPLY 1
Highlighted
Super User I
Super User I

@charchitjoshi 

 

Try this measure

 

Measure = VAR myyear=SELECTEDVALUE(Table1[Year])
RETURN
CALCULATE(average(Table1[Goodwill]),Table1[Year] in {myyear,myyear-1})
Regards,
Zubair


View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors