Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Exclude Expired Contract From Running Total

Hello,   I have been trying to resolve this for days. I`d be glad if you could help me to figure this out.   I`m have a running total measure which works fine. This measure shows the running tota...
Running Totals
  • Icey's avatar
    Icey
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hello,

     

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

     

    Annualized Sales Cumulative (Excluding Expired Contracts) =
    VAR mindate = MIN ( 'Journal Entries'[Accounting Date].[Date] )
    VAR maxdate = MAX ( 'Journal Entries'[Accounting Date].[Date] )
    RETURN
    CALCULATE (
    [Annualized Sales Amount],
    ALLSELECTED ( 'Journal Entries' ),
    'Contract Details'[Contract Effective Date] <= maxdate,
    'Contract Details'[Contract Expiration Date] >= mindate
    )