Average of selected last n years

[Pauet]

Hi everyone, I am trying to solve a problem I have when filtering by year in my pbi report.

Here is the Schema I have, the highlighted tables are the ones needed to solve the problem:

In the table "Procedencia_Paciente' I have the IDpac which is the id for every patient and what I want is to count them and group them by year and then compute an average for the year selected until the first year. For example if I select 2020 in my report filter, compute the count from 2014 up to 2020 and then divide over difference of years, in this case 6 in order to compute the average. 

The problem I have is that I cannot remove the average for those years which are not selected. I want to display only the records up to the selected year. The measure works fine, I mean the number is correct, however I is displaying all the years.

You can see it in the following plot:

*Note that the filter affects all the pages of the report and it only enables single selection and the year plotted is from "Calendario_Puente" table which contains the inactive relationship.

- Ligth blue line is the result of this measure:

- Dark blue line is the result of the Average measure:

Please how could I fix this?? I do not want to display all the years which my table has records. Thanks in advance!!

[amitchandak · Accepted solution]

@Pauet , if you just need average . Prefer to separate date/year table

= CALCULATE(AverageX(Values('Date'[Year]), CALCULATE(SUM(Sales[Sales Amount]))),filter(all('Date'),'Date'[Year] <=max('Date'[Year])))

But if you need show a trend too, the filter needs to be on an independent table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Year])
var _min = _max -6
return
calculate( averagex(Values(Date[Year]) , calculate( sum(Table[Value])) ), filter('Date', 'Date'[Year] >=_min && 'Date'[Year] <=_max))

[Pauet · Accepted solution]

Hi,

Your code and mine are correct, yours more elegant but it did not work, unfortunately. The solution was much simpler, the code needed an if statement for the measure that displays the light blue line, as you can see in the image:

Anyway, thank you so much mate!! It can be accepted as a solution😃

[amitchandak · Accepted solution]

@Pauet , if you just need average . Prefer to separate date/year table

= CALCULATE(AverageX(Values('Date'[Year]), CALCULATE(SUM(Sales[Sales Amount]))),filter(all('Date'),'Date'[Year] <=max('Date'[Year])))

But if you need show a trend too, the filter needs to be on an independent table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Year])
var _min = _max -6
return
calculate( averagex(Values(Date[Year]) , calculate( sum(Table[Value])) ), filter('Date', 'Date'[Year] >=_min && 'Date'[Year] <=_max))