Get data from another table with several filters

[Terrassa]

Hello,

I have two tables related to Bill Of Materials, one with the structure and another one with the revisions of the child components and their validity dates:

Table BOM:

Parent	Child
Car1	Wheel1
Car1	Tyre2
Car2	Wheel1
Car2	Tyre1
Car3	Wheel2
Car3	Tyre1

Table Revisions:

Material	Revision	Start	End
Wheel1	001	01/01/2021	31/03/2021
Wheel1	002	01/04/2021	31/07/2021
Wheel1	003	01/08/2021	31/12/2021
Wheel2	001		31/05/2021
Wheel2	002	01/06/2021
Tyre1	001		31/07/2021
Tyre1	002	01/08/2021	31/12/2021
Tyre2	001	01/01/2021	30/06/2021
Tyre2	002	01/07/2021

I need to find the active revision for each child component given a date selected on a slicer, so the selected date is between the start and end date of the revision, and show it in a table visual with 3 columns: Parent, Child and Revision.

The child material appears multiple times in both tables. I've tried to setup a many to many relationship but I'm having a lot of issues, so if possible, I would like to do it without relationships.

Examples of desired outcome (dates in format dd/mm/yyyy):

If the selected date is 2/7/2021 the visual should show:

Parent	Child	Revision
Car1	Wheel1	002
Car1	Tyre2	002
Car2	Wheel1	002
Car2	Tyre1	001
Car3	Wheel2	002
Car3	Tyre1	001

If the date is 1/5/2021:

Parent	Child	Revision
Car1	Wheel1	002
Car1	Tyre2	001
Car2	Wheel1	002
Car2	Tyre1	001
Car3	Wheel2	001
Car3	Tyre1	001

If the date is 1/12/2021:

Parent	Child	Revision
Car1	Wheel1	003
Car1	Tyre2	002
Car2	Wheel1	003
Car2	Tyre1	002
Car3	Wheel2	002
Car3	Tyre1	002

I don't know how to find the active revision and show it in a table visual. Could you please help me?

[rajendraongole1 · Accepted solution]

Hi @Terrassa - create one new date table with some range using calendar function. 

check the model and relationship with the new date table.

use the below measure to get the active revision and call the date table call it in selectedvalue(datetable[date]). 

ActiveRevision =
VAR SelectedDate = SELECTEDVALUE(DateTable[Date])
RETURN
CALCULATE(
MAX(Revisions[Revision]),
FILTER(
Revisions,
Revisions[Material] = MAX(BOM[Child]) &&
Revisions[Start] <= SelectedDate &&
(Revisions[End] >= SelectedDate || ISBLANK(Revisions[End]))
)
)

try the above measure and let know if it works 

[Terrassa]

Thank you very much Rajendraongole1, it works perfectly!