Simple relationship does not work (1 fact table, 1 lookup table)

[chefe]

Hi there,

• I have a data/fact table called "IR positions MTM", containing transactions data for several days, including a field which represents the date on which the transaction is settled.
• Also, there is a date table (lookup table) "Calendar - settlement dates". Marked as Date table in SSDT.
• I have connected both tables with a 1:many relationship. I made sure both fields are of type "date".

So far so good. However, the relationship does not seem to work. I don't know why.

There are two measures: 

1. on the fact table itself using the MAX function to get the latest [period_settlement_date]
2. on the date table, using the LASTDATE function (also tried the MAX function here, didn't work either)

The problem is that only #1 works. #2 always shows the absolutely last entry in the date table. Even trying LASTDATE(ALLSELECTED(...)) didn't work.

I have attached a screenshot to this setup below. Does anyone spot the problem? This is driving me crazy, far more complex calculations work, but this doesn't... 😕

Kind regards, chefe

[chefe]

P.S.: 

does it it matter where (on what table that is) the measure is stored?

[chefe]

Apparently not, have tried inputting the formulas into both tables as a measure. Doesn't work either.

[Anonymous]

Hi @chefe,

Could you please post expected result here?  Do you want to calculate max date of fact table using LASTDATE function? If so, please create measure using DAX below.

LastDate = LASTDATE(IR positions MTM[period_settlement_date])

Thanks,
Lydia Zhang