Exclude Expired Contract From Running Total

Hi Anonymous ,

Please check if this could work in your scenario:

Annualized Sales Running Total (Expired Exluded) =
VAR Contract_Expiration_Date =
    CALCULATE (
        MAX ( 'Contract Details'[Contract Expiration Date] ),
        FILTER (
            'Contract Details',
            'Contract Details'[Contract Expiration Date]
                < MAX ( 'Journal Entries'[Accounting Date] )
        )
    )
RETURN
    CALCULATE (
        'Journal Entries'[Annualized Sales Amount],
        FILTER (
            ALLSELECTED ( 'Journal Entries' ),
            'Journal Entries'[Accounting Date] <= MAX ( 'Journal Entries'[Accounting Date] )
                && 'Journal Entries'[Accounting Date] > Contract_Expiration_Date
        )
    )

If it doesn't work, please share your sample .pbix file without sensitive information.

---

 

I created an example Power BI and excel file to demonstrate the case but i can't attach it to my messeage for some reason, so I have to try to explain in this post.

 

---

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

Sorry for the late reply on this. I was able to reach the desired result by using the formula below: