Calculate value for two date columns

DAX DAX Calculations
1

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

image

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:

Dummy%202

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

Thanks

2

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

Something like the below.

image

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

image
image.png719×274 21.4 KB

Change the slicer to the below

image

This will give you the one date.

image

Then calculate this…

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

image

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.

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

Thanks
Sam