Measure: Finding total of still open

[LauraCawdronDav]

Hi, 

A bit of a complicated one, I'm looking to create a measure where I calculate the total value of opportunities open in various different months. Something being "open" is classed as anytime between the "Brief received on" date and "Closed on" date. Opportunities could be open for a few months, and the below data is just a selection. I've left some mock data below as well as the expected outcome 

Data:

Opportunity Name	Brief received on	Closed on	Value
Opportunity A	04/04/2022	04/06/2022	£20
Opportunity B	11/05/2022	11/07/2022	£30
Opportunity C	02/05/2022	13/05/2022	£10
Opportunity D	17/04/2022	17/05/2022	£20
Opportunity E	06/06/2022	still open	£10
Opportunity F	22/06/2022	26/06/2022	£50
Opportunity G	08/03/2022	still open	£30

And this is what I would like to see from that:

Month	Total Value Open	Workings (not needed by as reference)
April	£70	A (20) + D (20) + G (30)
May	£110	A (20) + B (30) + C (10) + D (20) + G (30)
June	£140	A (20) + B (30) + E (10) + F (50) + G (30)
July	£70	B (30) + E (10) + G (30)

I think the measure just needs to find the total when "open" and then I can split this out by month after that, but I'm struggling to work it all out!

Laura

[johnt75 · Accepted solution]

Try

Total Open Value =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        REMOVEFILTERS ( 'Date' ),
        'Table'[Brief received on] <= ReferenceDate
            && (
                'Table'[Closed on] > ReferenceDate
                    || ISBLANK ( 'Table'[Closed on] )
            )
    )
RETURN
    Result

[LauraCawdronDav]

Hi,

This does look really close thank you! It does still seem to be having some issues though.

Please can I ask what 'Table'[ReferenceDate]' refers to? It doesn't seem to like that part of the formula. Should this reference the date table? 

Thanks,

Laura 

[johnt75]

That's my mistake, it should be 'Table'[Closed on]. I have edited my post to reflect that.

[LauraCawdronDav]

yes that's it - thank you much!!!