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

Hi PBIX1234 
How does your report look like?

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    PBIX1234 
    What should be the result?

    • PBIX1234's avatar
      PBIX1234
      Frequent Visitor
      3 years ago

      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'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]
    )
)