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 
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]
    )
)
      • 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 🙂