Filter two fact table date columns with single date dimension table and inactive relationships

[Anonymous]

A fact table contains accumulated daily snapshots of term deposit data from a bank system. I want to calculate the amount that will have matured on future dates given maturity information as of the most recent snapshot date.

I have a single 'Calendar' date dimension table that has inactive relationships to (among others) both 'Deposits'[Snapshot Date] and 'Deposits'[Maturity Date].

USERELATIONSHIP('Deposits[Maturity Date], 'Calendar'[Date]) allows me to plot maturity amounts along 'Calendar'[Date], but I don't know how to most efficiently apply a single date filter to 'Deposits'[Snapshot Date].

I could duplicate 'Calendar' as the Role-Playing 'Calendar (Snapshot Date)' table, define an active relationship, and apply a filter like 'Calendar (Snapshot Date)'[Date] = '2023-02-05' to my model.

The downside is the engine must scan the addtional date table and transfer the filter.

Alternatively, I could just apply the Snapshot date filter directly to the fact table, as in 'Deposits'[Snapshot Date] = '2023-02-05'. That would make the engine inefficiently scan the still very large subset of fact table rows where Maturity Date = 'Calendar'[Date].

I know two relationships cannot be active between two tables at the same time but are there any tricks that permit a better way to do this?

The fact table will quickly grow to 100's of millions of rows, so I'm looking for the most efficient implementation possible.

[amitchandak]

@Anonymous , First create meausre with use relationship

m1= calculate( SUM(Table[Amount]),USERELATIONSHIP ('Table'[Maturity Date], 'Calendar'[Date]))

m2= calculate( SUM(Table[Amount]),USERELATIONSHIP ('Table'[Snapshot Date], 'Calendar'[Date]))

Then create cumulative measures

examples

Cumm Sales = CALCULATE([M1],filter(all('Calendar'),'Calendar'[date] <=max('Calendar'[date])))