Forum Discussion

PBIX1234's avatar
PBIX1234
Frequent Visitor
3 years ago

Measure with multiple dates & an exception

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...
DAX
Need Help
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
PBIX1234
Frequent Visitor
3 years ago

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 🙂