Average of filter not giving the expected value

[Anonymous]

Hi,

I have two tables, what I want the column to do is to calculate the last value of the column number in the new column, given that I don't have the number for 2020 I am fixing the value of year in the second line of the formula. However the formula does not work as expected only working for the rows of 2019 not 2018,2020.

Cuadrante = IF(ISBLANK(CALCULATE(FIRSTNONBLANK(T2[NUMBER],TRUE()),FILTER(T2,T2[YEARDNI]=T1[YEARDNI]))),
CALCULATE(FIRSTNONBLANK(T2[NUMBER],TRUE()),FILTER(T2,T2[YEARDNI]=CONCATENATE("2019",T1[DNI]))),
CALCULATE(FIRSTNONBLANK(T2[NUMBER],TRUE()),FILTER(T2,T2[YEARDNI]=T1[YEARDNI])))

Table 1

YEARDNI	YEAR	DNI	NEWCOLUMN
20201	2020	1	8
20191	2019	1	8
20181	2018	1	3

Table 2

YEARDNI	YEAR	DNI	NUMBER
12019	2019	1	8
12018	2018	1	3

Please your kind help

[Greg_Deckler]

I'm not sure I completely understand. Is the data you show the source data, expected output, both? Which is which? I do see in your formula that you hard coded "2019" so that probably explains why it is working for 2019 and not 2020 and 2018. You would need to replace that with something like [Year]?

[Anonymous]

Hi,

Table 1 is the expected output, Table 2 is the source data. What I want it to do is to select the 2019 when there is no 2020 in the source data but when hardcoding it only works for the Table 1 Year 2019.

Thanks for the help,

[Anonymous · Accepted solution]

Ended up duplicating the data of the year 2019 and changing it to 2020. It works for now