Display specific dimension value based on the filter on date

[ovonel]

I am unable to achieve the next DAX. I have 2 tables: Revenue and Type.

I want to display: latest type, at the selected FY  of Revenue in a filter. (That is, if FY 20 is selected, the type shall be the valid one on 31-May-2020).

ie Revenue table:

Revenue	FY	Key Type
10	19	1000
54	19	1001
156	20	1002
15	20	1003
11	20	1004
12	21	1005
48991	20	1006

Type table:

Key Type	Customer	Type	ValidFrom	ValidTo
1000	15	1	01-jan-18	02-jan-18
1001	15	2	02-jan-18	02-fe-18
1002	15	3	03-feb-18	02-oct-21
1003	15	4	03-dec-21	02-jan-22
1004	15	5	03-jan-22	02-ma-22
1005	15	6	03-may-22	NULL
1006	492	2	01-jan-16	NULL

(The filter on date shall be for the Type, not for the revenue).

When the user selects FY20, I want to display all Revenue data like:

Segm	Revenue	FY	Type
1000	10	19	3
1001	54	19	3
1002	156	20	3
1003	15	20	3
1004	11	20	3
1005	12	21	3
1006	48991	20	2

• on 31-May-2020, the valid Type was 3 for customer 15; and Type 2 for customer 492.

When the user selects FY21, the same shall be displayed since on 31-May-2021, for customer 15 the valid Type was 3 and Type 2 for customer 492.  

When the user selects FY22, I want to display:

Segm	Revenue	FY	Type
1000	10	19	6
1001	54	19	6
1002	156	20	6
1003	15	20	6
1004	11	20	6
1005	12	21	6
1006	48991	20	2

• on 31-May-2022, the valid Type was 6 for customer 15
• on 31-May-2022, the valid Type was 2 for customer 492

Is this possible? (I am also concerned performance-wise)

[NikhilChenna]

Hi @ovonel ,

I think below solution will help you. 

1. Create a one to many relationship between the two table revenue and type based on the key type column. 

2. Create a similar relation ship of one to many from the date tabe to type table based on the date column. 

this will solve the issue. 

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!