look up value for a date using YEAR

[ØysteinTv]

Hey

I have a table with users:

User	Period	Account	Extra1
user1	2020-01-01	Full	A
user1	2021-01-01	Full	B
user2	2020-01-01	Mobile	B
user2	2021-01-01	Full	B

EDIT: changed user table to clarify that User is a text and not number field.

And a table with account costs:

Category	SubCat	Cost	Year
Account	Full	200	2020
Account	Full	220	2021
Account	Mobile	100	2020
Account	Mobile	110	2021
Extra1	A	50	2020
Extra1	A	60	2021
Extra1	B	100	2020
Extra1	B	110	2021

Now I know how to lookup the cost if there is just one value - but how do I match the year as well? 
Can it be done in a measure or do I need to make a column in the User-table? 

And can I make a Matrix that basically is something like this (with a date-slide selected for sometime in 2020 in the example below)

ID	Account		Extra		Total
	Full	Mobile	A	B
1	200		50		250
2		100		100	200

[v-zhenbw-msft]

Hi @ØysteinTv ,

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.

[ØysteinTv]

Hi @v-zhenbw-msft 

my bad for just using 1 and 2 as users, the user field is a text field and not a number. 

I think my primary concern is how to look up the year in the value table, and using the Category to find the right cost value. 

I'm leaning towards splitting the cost table into seperate tables which are each linked to the main USer-table and running lookups to each seperate table.

But still need to understand how to check the year 😄