DATEDIFF between two different tables not working

[FGN]

Hi,

first some context: I'm working with a big dataset I can't modify (can't create columns) so I have to work only with measures. I've been working with DAX only for a couple months so probably I'm making a silly mistake, but I can't see it.

I made this measure and it works as I need it.

Venta Inn =

CALCULATE(

    [Venta Total],

    MaterialComercial[Atributo_1]="x",

    DATEDIFF('Venta y Presupuesto'[Primera_venta_material],'Venta y Presupuesto'[Fecha],YEAR)<=4

)

DATEDIFF uses [Primera_venta_material] that is the date of the first sale, but now I need to use not the date of the first sale, but the date the material was created in the system, that is in another table Material [Fecha_de_creacion_del_registro]

I use the RELATED function inside DATEDIFF expecting to work, but it doesn't.

This is what I have:

Venta Inn =

CALCULATE(

    [Venta Total],

    MaterialComercial[Atributo_1]="x",

    DATEDIFF(RELATED(Material [Fecha_de_creacion_del_registro]),'Venta y Presupuesto'[Fecha],YEAR)<=4

)

It tells me that Material [Fecha_de_creacion_del_registro] does not exist or does not have a relation....

Here is how the relation between the two tables is working

What am I missing? Thanks!

[amitchandak · Accepted solution]

@FGN , Try like

Venta Inn =
CALCULATE(
[Venta Total],
MaterialComercial[Atributo_1]="x",
Filter('Venta y Presupuesto' ,DATEDIFF(RELATED(Material [Fecha_de_creacion_del_registro]),'Venta y Presupuesto'[Fecha],YEAR)<=4)
)

make sure the join is active

[FGN]

Thank you! I tryed it and it worked. Rigth now I don't undestand why but I'm sure a little nice reading will reveal the magic.