Sum of values of on table according to existence in other in same period

[Ritaf]

Hi, 
I have a table of purchase transactions of products and dates,
and a table of warehouse entries by dates.
I need to sum of values of purchases just for products with at least one entry on the year with the purchase like on the picture.
Is this the way to do it with DAX?
The link for the sample PBIX:
https://drive.google.com/file/d/1Or0XWXEYVEGYirSQfJUyIGObgw9G3mlm/view?usp=sharing

. attached

[DataInsights · Accepted solution]

@Ritaf,

Try this measure. I added a relationship between the calendar and entries tables. To confirm the logic: sum the purchase value for each product that has at least one row in the entries table (for the selected year).

Purchases with Entry =
CALCULATE (
    SUM ( purchase[value] ),
    FILTER ( products, NOT ISEMPTY ( RELATEDTABLE ( entries ) ) )
)

[v-henryk-mstf]

Hi @Ritaf ,

Whether the advice given by @DataInsights  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[DataInsights · Accepted solution]

@Ritaf,

Try this measure. I added a relationship between the calendar and entries tables. To confirm the logic: sum the purchase value for each product that has at least one row in the entries table (for the selected year).

Purchases with Entry =
CALCULATE (
    SUM ( purchase[value] ),
    FILTER ( products, NOT ISEMPTY ( RELATEDTABLE ( entries ) ) )
)

[Ritaf]

Thank you!
This solution is perfect in this context.
One more question:
If the model is built so that there is a connection between purchase transactions and entries by purchaseitemid ( not by-product like in my sample file) is this the way to handle it with DAX or do I need to build this logic in the model itself?

[DataInsights · Accepted solution]

@Ritaf,

Glad to hear the solution works.

If the connection between the purchase and entries tables is PurchaseItemID, the DAX would need to be modified. In a star schema, there aren't relationships between fact tables (the relationships are between dimension and fact tables), which prevents you from using RELATEDTABLE as it exists in the measure.