Filter Calendar dimension table based on fact table value

[NegaStar]

Hi All,

I would like to ask your help to find a solution of the following case.

I would like to get the minimum Calandar[Date] value which is equal or greater than TransctionPast[FirstValid] date.

Here are the relationships:

I created the following measure but it just uses a lot of RAM without any result, when I tried to put it in it in a power pivot's value section

I think it is caused by the relationship and the filter direction but I can not figure out how to get the required date value.

I would use it for further calcualtion which will be in the value section of a power pivot.

Could you help me to define the required measure? Please see the sample file on the following link. Thank you very much in advance!
Power Pivot Sample

 

[Matt Allington]

I'm not clear what you are trying to do. The calendar[date] column is joined to 2 columns
TransactionPast[YearMonth_FirstDate]
TransactionFuture[YearMonth_FirstDate]

I assume therefore that you are wanting to know the minimum of these 2 tables.
maybe like this
=min(min(TransactionPast[YearMonth_FirstDate]),min(TransactionFuture[YearMonth_FirstDate]))

but I think your model is more complex than it needs to be. Consider appending the 2 transaction tables together and add a new "Type" column for Past and Future

 

[NegaStar]

I'm not clear what you are trying to do. The calendar[date] column is joined to 2 columns
TransactionPast[YearMonth_FirstDate]
TransactionFuture[YearMonth_FirstDate]

I assume therefore that you are wanting to know the minimum of these 2 tables.
maybe like this
=min(min(TransactionPast[YearMonth_FirstDate]),min(TransactionFuture[YearMonth_FirstDate]))

but I think your model is more complex than it needs to be. Consider appending the 2 transaction tables together and add a new "Type" column for Past and Future

Yes, you are right, meanwhile I did it on the same way and appended the future and past transaction tables. I found the problem of the high memory usage too, it was caused by the missing FirstValid date in the related filter context of power pivot table. Thank you very much for your quick response!