Creating As of Date Filter for the Page

[jonboy444]

I would like to create a report date style filter, where a single date is picked and it captures all the trades that are active, i.e. where trade date < report date < maturity date. 

So far I am able to get the trade date < report date by selecting a single date. I followed https://www.youtube.com/watch?v=xZcO83X5K30&t=191s this video. 

I now have 3 tables, 'All Issuance', 'DateTable', and 'DisconnectedDate' 

All Issuance and DateTable have a many-to-one relationship on trade date. There is an inactive relationship between the maturity date and the date table. 

To show it filtering one way, see below: It also includes the measure for outstanding. the Stop date measure works as the selected date basically, 

Stop date = MAX(UnconnectedDate[Date]). 

My question is, currently, my outstanding USD measure: 

Outstanding usd = CALCULATE(SUM('All Issuance'[USD Equiv. Amt]),FILTER(DateTable,DateTable[Date]<=[Stop date]))

Is correctly filtering out the deals that haven't in effect been traded yet because their trade date is in the future to the selected report date. However, I also want this measure to filter out those deals with a maturity that is less than the stop date since these are out of scope. Hope this makes sense, thanks

[v-junyant-msft · Accepted solution]

Hi @jonboy444 ,

I'm not sure if this is what you need, because you didn't provide sample data, I had to create a data table myself based on your description:

I can't confirm if the report date and Stop date you mentioned are the same date, so I can only write DAX by saying that they are both the same date:

Outstanding usd = 
VAR Stopdate = MAX('UnconnectedDate'[Date])
RETURN
CALCULATE(
    SUM('All Issuance'[USD Equiv. Amt]),
    FILTER(
        ALL('All Issuance'),
        'All Issuance'[trade date] <= Stopdate && 'All Issuance'[maturity date] >= Stopdate))

And here is the final output:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rupak_bi]

below is the ilustration of the formula. not exact formula. plz share sample table to try exact solution if below not works.

Outstanding usd = CALCULATE(SUM('All Issuance'[USD Equiv. Amt]),FILTER(all('All Issuance),trade date <= DateTable[Date]&& calculate(max(maturity date),userrelationahip(maturitydate,datetable date)>=date table date)

[Rupak_bi]

use "userrelationship" function to activate that second inactive relationship. your job will be done.

[jonboy444]

But how could I use it in this dax, Outstanding usd = CALCULATE(SUM('All Issuance'[USD Equiv. Amt]),FILTER(DateTable,DateTable[Date]<=[Stop date]))