Measure with multiple dates & an exception

[PBIX1234]

Hi, I am working with a model that has two fact tables joined by some shared dimensions.

The fact tables are our payments named SPEND (top left) and contract records named CONTRACTS (bottom right)

They are joined by Date & Supplier that are common, as well as Categories which has some mapping.

Screenshot of the model;

I am trying to create a measure for Contracted Spend. The conditions are;

Contracted Amount = 

Sum of SPEND(Amount)

Occurs after CONTRACTS(Start)

AND

Occurs before CONTRACTS(End) ... *UNLESS the CONTRACTS(Status) is "Active"

*The last condition is to account for contracts that are expired but rolling on terms - this is how our system tracks it.

I tried first getting the result excluding the exception of active contracts after expiry, but still didn't get the desired result;

Contracted Amount =

    CALCULATE(

                SUM(SPEND[AMOUNT]),

                CONTRACTS[START]<MAX('DATE'[Date]),

                CONTRACTS[END]>MAX('DATE'[Date]),

     )

How would this formula be phrased?

Many thanks!

[tamerj1]

Hi @PBIX1234 
How does your report look like?

[PBIX1234]

Hi @tamerj1 ,

Here is a sample file.

[tamerj1]

@PBIX1234 
What should be the result?

[PBIX1234]

@tamerj1 , technically the result on the sample file is correct, all should be "contracted". However, it is not calculating correctly, as the current measures don't account for "if active after expiry date". Hence, 2021 should show as unconctracted without the exception. 

I started a new measure called "Contracted Amount (2)" to compare with - this is what I need the formula help with.

Thank you 🙂

[tamerj1]

@PBIX1234 
Please check this measure and let me know if this is what you want.

Contracted Amount (2) = 
SUMX (
    ALL ( CONTRACTS ),
    SUMX ( 
        FILTER ( 
            SPEND,
            SPEND[NAME] = CONTRACTS[NAME]
                && SPEND[DATE] >= CONTRACTS[START]
                && OR ( 
                    SPEND[DATE] <= COALESCE ( CONTRACTS[EXPIRY], TODAY ( ) ),
                    CONTRACTS[STATUS] = "Active"
                )
        ),
        SPEND[AMOUNT]
    )
)

[PBIX1234]

@tamerj1 ,

Thank you - it did return the correct result but when I tested another sample dataset it was not correct. I am also concerned about using ALL(Contracts), as this will remove the ability to filter by Category later down the track for example.

I have a new sample dataset here, it may be clearer. Can you please perhaps have another look?

Appreciate your help!

Thanks 🙂