Using count of selected years to calculate average

[OliverMatsik]

Hi,

I hope someone can help me out here 🙂

I have a graph which shows 2020 Revenue trend per month and another line which is 2017 - 2019 AVG, so you can see how much we differ from the trend and also what we can expect for the rest of the year based on history.

Below that there is another one which shows the same but showing the yearly totals.

I would like to 2017 - 2019 AVG line to dynamicly calculate based on the selection in the table below. So if you only click on 2019 then it basically compares 2019 to 2020, if you select 2018 + 2019 then it only devides thes SUM by 2 and not 3.

For this I wrote a measure to count the selected years (and exclude 2020): 

Selected years count = IF(CONTAINS(FinancialData , FinancialData[Date].Year] , 2020)=TRUE,

CALCULATE(DISTINCTCOUNT(FinancialData[Date].[Year]) , FinancialData[Date])-1,

CALCULATE(DISTINCTCOUNT(FinancialData[Date].[Year]) , FinancialData[Date]))

I checked and the calculation returns a number between 1 and 3, and it is a number. So I thought so far so good...

Then I wanted to devide the sum of 2017 + 2018 + 2019 Revenue by the above. There is no syntax error but the calculation simply doesn't work

2017 - 2019 AVG Revenue = (FinancialData[2017 Revenue]+FinancialData[2018 Revenue]+FinancialData[2019 Revenue]) / [Selected years count]

If I devide it simply by 3 it works perfectly but of course it is not dynamic and if you only select on or 2 years the calculation is off.

Your support is much appreciated.

Oliver

[amitchandak]

@OliverMatsik , Try like this with a date table

Avg last 3 year = if(isbalnk(SUM(FinancialData[2017 Revenue]),(CALCULATE(SUM(FinancialData[2017 Revenue]),DATESMTD(dateadd('Date'[Date],-1,year)))+CALCULATE(SUM(FinancialData[2017 Revenue]),DATESMTD(dateadd('Date'[Date],-2,year))) +CALCULATE(SUM(FinancialData[2017 Revenue]),DATESMTD(dateadd('Date'[Date],-3,year))))/3)

[OliverMatsik]

Thanks, I created a date table, but it somehow messed up my current date information, my graphs crashed.

Can you advise how can I make my calculation work maybe? If the calculated measure works well, it calculates the number of selected years correctly, why cant I use this number to simply divide the selected years' totals with the number of years?