Calculate value for two date columns

Hi All,

I have this table of data. I want to find the value of OPEN Ops at any given date with the ability to look back in time to get a snapshot


How can I create a virtual table that is then used to calculate opp value dynamically when looking at 2 different date columns ( I understand the inactive relationship and userelationship but can’t get this to work for this scenario)

e.g.Date as of = 30/6/2019 (this would be Event Dates filter)`

Virtual Table would comprise all opportunites where:

Opportunity[CreateDate] <= 30/6/2019 
Opportunity[ClosedDate] >30/6/2019
Opportunity[ClosedDate] IsBlank

I’d like to end up with this table without using Visual Level filters:


Help please - I’ve included my model for reference:
Dummy Data File 3 - Copy.pbix (213.7 KB)


I believe here you just want to create a new date table that you would use as the slicer.

Something like the below.


This would then become the way you would create the dynamic break in your formula.

Change the slicer to the below


This will give you the one date.


Then calculate this…

Date Selection = MIN( 'Date Slicer'[Date] )


Now you have a date you can integrate into your formulas to breakout the amounts. For example…

Total Opp after Date Selection = 
CALCULATE( [Total Opp £],
    FILTER( ALL( Opportunity ), Opportunity[CreateDate] > [Date Selection] ) )

See how you go working with this idea.

Dummy Data File 3 - Copy.pbix (211.1 KB)