'Problem in using filter context in CALCULATE

In PowerPivot DataModel I have written following DAX Code:

=VAR var1 =
    CALCULATE (
        SUMX ( 'CapitalPrepayment', 'CapitalPrepayment'[Amount] ),
        'CapitalPrepayment'[Subject] = "Proforma",
        ALL ( 'CapitalPrepayment'[EffectiveYear] )
    )
VAR var2 =
    CALCULATE (
        SUMX ( 'CapitalPrepayment', 'CapitalPrepayment'[Amount] ),
        'CapitalPrepayment'[EffectiveYear] = "1399",
        'CapitalPrepayment'[Subject] = "Invoice"
    )
VAR var3 =
    CALCULATE (
        SUMX ( 'CapitalPrepayment', 'CapitalPrepayment'[Amount] ),
        'CapitalPrepayment'[Subject] = "Proforma",
        'CapitalPrepayment'[EffectiveYear] = "1398"
    )
VAR var5 =
    CALCULATE (
        SUMX ( 'CapitalPrepayment', 'CapitalPrepayment'[Amount] ),
        ALL ( 'CapitalPrepayment'[Subject] )
    )
RETURN
    IF ( Var5 < 0, 0, IF ( Var5 = 0, 0, IF ( VAR1 + VAR2 <= 0, Var3, 0 ) ) )

and the result is: enter image description here

As you can see, the problem is that grand total of [Var5] for some vendors and grand total for [Final] for all vendors is highly related to filters come from EffectiveYear ( at least Ithink so ), so the result would be zero. in addition, changing the filter context from visual ( for example removing [vendor] and [EffectiveYear] ) will disrupt the result. how can I fix this?

In my written DAX, [Var1]~[Var3] do include [subject] and [EffectiveYear] as filter arguments, but [Var5] does not for [EffectiveYear] . so I expect that the problem arise from [EffectiveYear]. I tried to include [EfectiveYear] in someway in CALCULATE but it didn't work.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source