Calculate last of dates for related records in related table

[mblom]

As a novice power bi user I am not able to figure this out:

Model: minox-artikel.artikelcode = minox-facthrgl.artikelcode and minox-facthrgl.factuurnr = minox-facthkop.factuurnr

For each record in minox-artikel I would like to calculate MAX facthkop.factuurdatum

I tried creating a new measure: 

LastSalesDate = CALCULATE(MAX('minox-facthkop'[FACTUURDATUM]))

But when filtering the result for just one minox-artikel record (1005181) the result shows many, seemingly unrelated, records:

Can anyone please help me to get the one and only MAX date from minox-facthkop for a given record in minox-artikel?

Thanks!!

[amitchandak]

@mblom , two example how can you move data from one table to another a new column

Item Name = RELATED('item'[Brand])
City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

or

Item Name = maxx('item',RELATED('item'[Brand]))

[Anonymous]

Where is  that ArtikelCode coming from? Your facthkop table is only filtered by Date table and another table which I couldn't see in your picture.

Check your relationship between tables. The direction shows the way that filter can propagate. In this case if you want the ArtikelCode to filter your fact table, the table that contains ArtikelCode column should have a directed way to go to your facthkop table. Otherwise, your facthkop table will not be filtered during the calculation and will always return you the largest date in that table.

https://docs.microsoft.com/en-us/analysis-services/tabular-models/relationships-ssas-tabular?view=as...

Reference

[mblom]

Thanks for your reply!

To simplify the model:

table Product: this contains a list of products with Productcode as unique key

table Invoice: this contains invoice header records with Invoicenumber as unique key and Invoicedate as one of the other columns

table InvoiceItems: this contains invoice items with (amongst other) Invoicenumber as a reference to Invoice and Productcode as a reference to Product

What I try to achieve is: for every record in Product calculate the MAX of Invoicedate in table Invoice.