Getting the last transaction date when using the date dimension

[MCornish]

I have a model that (simplified) is:

Fact = Entries

Dimension = Date (marked as date table)

Dimension = Product

When I try to get the last date a product was sold I get the max date of my date dimension. I can't use the Fact table as the date key is an integer (20190820).

How can I tell PBI to only get it for the current row bound by the fact table?

[TeigeGao]

HI @MCornish ,

In your scenario, you will need to create a date key (int data type) column in the Dimension = Date (marked as date table) table, then we can create relationship between these tables. 

Best Regards,

Teige

[MCornish]

---

@TeigeGao wrote:

HI @MCornish ,

In your scenario, you will need to create a date key (int data type) column in the Dimension = Date (marked as date table) table, then we can create relationship between these tables. 

Best Regards,

Teige

---

@TeigeGao The relationship already exists

All i can get when using ProductID from Products and the Date from the Date dimension is this

[MCornish · Accepted solution]

Ok. I kind of sorted it.

LOOKUPVALUE( Dates[Date], Dates[DateKey], MAX( 'Product Entries'[PostingDateKey] ) )

Not the most elegant of solution and I'm sure it can be improved upon. So please feel free to try and improve upon it.

[Anonymous]

Can you upload a sample file?