look up value for a date using YEAR

Hi Anonymous ,

We can add an ID column in table with account costs to meet you requirement.

1. Create a year column in table with users.

Year = YEAR('Table'[Period])

2. Then we can create an ID column.

ID = 
var x = CALCULATE(SUM('Table'[User]),FILTER('Table','Table'[Account]='Table (2)'[SubCat] && 'Table'[Year]='Table (2)'[Year] ))
var y = CALCULATE(SUM('Table'[User]),FILTER('Table','Table'[Extra1]='Table (2)'[SubCat] && 'Table'[Year]='Table (2)'[Year]))
return
IF(ISBLANK(x),y,x)

3. At last we can create a matrix visual, and filter the ID is 1 and 2. The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.