Subtracting measures based on different filters not working

[learning_pbi_11]

I am trying to subtract three measures and have the result show the same value in every row. The formula below returns 

VAR Test_Retained_earnings =

        CALCULATE(
                           Running_Total_ASSETS-
                           YTD_Net_Income_Acct_3900 -
                           Running_Total_Liabilities_Equity_less_NetIncome_less_RetainedEarnings,

                           ALL('Balance Sheet Information' )

                           )

       RETURN Test_Retained_earnings

Below is the output in a matrix visual. 
 
I would like BS_Running_Total6 to return ($1,751.36) for every value, not just the total. What filter must I apply to get this? The entire formula is pasted below: 

BS_Running_Total6 =

VAR _CurrentMonth = MONTH(MAX('Calendar'[Date] ) )

VAR _CurrentYear = YEAR(MAX('Calendar'[Date] ) )

VAR YTD_Net_Income_Acct_3900 =

    CALCULATE(

        SUM('Balance Sheet Information'[BS_$_Amount] ),

        'Balance Sheet Information'[AcctNum] = 3900,

        MONTH('Calendar'[Date] ) <= _CurrentMonth,

        YEAR('Calendar'[Date] ) = _CurrentYear

    )            

VAR MaxBSDate = MAX('Calendar'[Date] )

VAR MinDate =

    DATESINPERIOD(

        'Reference_Calendar'[Date],

        MaxBSDate,

        -240,

        MONTH

    )

VAR  Running_Total_All_Except_3900=

    CALCULATE(

        SUM('Balance Sheet Information'[BS_$_Amount] ),

        REMOVEFILTERS('Calendar'),

            KEEPFILTERS(MinDate ),

            USERELATIONSHIP('Calendar'[Date],'Reference_Calendar'[Date] )    

    )

VAR  Running_Total_ASSETS=

    CALCULATE(

        SUM('Balance Sheet Information'[BS_$_Amount] ),

        'Balance Sheet Information'[AcctNum] < 2000,

        REMOVEFILTERS('Calendar'),

            KEEPFILTERS(MinDate ),

            USERELATIONSHIP('Calendar'[Date],'Reference_Calendar'[Date] )    

    )

VAR  Running_Total_Liabilities_Equity_less_NetIncome_less_RetainedEarnings =

    CALCULATE(

        SUM('Balance Sheet Information'[BS_$_Amount] ),

        'Balance Sheet Information'[AcctNum] >= 2000,

        'Balance Sheet Information'[AcctNum] <> 3500,

        'Balance Sheet Information'[AcctNum] <> 3900,

        REMOVEFILTERS('Calendar'),

            KEEPFILTERS(MinDate ),

            USERELATIONSHIP('Calendar'[Date],'Reference_Calendar'[Date] )    

    )

VAR Test_Retained_earnings =

        CALCULATE(Running_Total_ASSETS - YTD_Net_Income_Acct_3900 - Running_Total_Liabilities_Equity_less_NetIncome_less_RetainedEarnings,

        ALL('Balance Sheet Information' )

            )

RETURN Test_Retained_earnings

[rajendraongole1]

Hi @learning_pbi_11 -You are almost there with your formula. The main adjustment is ensuring that ALL filters are removed across the table during the final calculation.

BS_Running_Total6 =

VAR _CurrentMonth = MONTH(MAX('Calendar'[Date]))
VAR _CurrentYear = YEAR(MAX('Calendar'[Date]))

VAR YTD_Net_Income_Acct_3900 =
CALCULATE(
SUM('Balance Sheet Information'[BS_$_Amount]),
'Balance Sheet Information'[AcctNum] = 3900,
MONTH('Calendar'[Date]) <= _CurrentMonth,
YEAR('Calendar'[Date]) = _CurrentYear
)

VAR MaxBSDate = MAX('Calendar'[Date])
VAR MinDate =
DATESINPERIOD(
'Reference_Calendar'[Date],
MaxBSDate,
-240,
MONTH
)

VAR Running_Total_ASSETS =
CALCULATE(
SUM('Balance Sheet Information'[BS_$_Amount]),
'Balance Sheet Information'[AcctNum] < 2000,
REMOVEFILTERS('Calendar'),
KEEPFILTERS(MinDate),
USERELATIONSHIP('Calendar'[Date], 'Reference_Calendar'[Date])
)

VAR Running_Total_Liabilities_Equity_less_NetIncome_less_RetainedEarnings =
CALCULATE(
SUM('Balance Sheet Information'[BS_$_Amount]),
'Balance Sheet Information'[AcctNum] >= 2000,
'Balance Sheet Information'[AcctNum] <> 3500,
'Balance Sheet Information'[AcctNum] <> 3900,
REMOVEFILTERS('Calendar'),
KEEPFILTERS(MinDate),
USERELATIONSHIP('Calendar'[Date], 'Reference_Calendar'[Date])
)

VAR Test_Retained_earnings =
Running_Total_ASSETS -
YTD_Net_Income_Acct_3900 -
Running_Total_Liabilities_Equity_less_NetIncome_less_RetainedEarnings

RETURN
CALCULATE(
Test_Retained_earnings,
REMOVEFILTERS('Balance Sheet Information'),
REMOVEFILTERS('Calendar') -- Ensures same value across all rows
)

hope this works at your end.

[learning_pbi_11]

Hmm - I copied the formula you provided and renamed it "BS_Running_Total7" and it returns the same result. 

I am not sure why it is still doing this? Repasting the formula below so you can see it is unchanged: 

BS_Running_Total7 =

VAR _CurrentMonth = MONTH(MAX('Calendar'[Date]))

VAR _CurrentYear = YEAR(MAX('Calendar'[Date]))

VAR YTD_Net_Income_Acct_3900 =

CALCULATE(

SUM('Balance Sheet Information'[BS_$_Amount]),

'Balance Sheet Information'[AcctNum] = 3900,

MONTH('Calendar'[Date]) <= _CurrentMonth,

YEAR('Calendar'[Date]) = _CurrentYear

)

VAR MaxBSDate = MAX('Calendar'[Date])

VAR MinDate =

DATESINPERIOD(

'Reference_Calendar'[Date],

MaxBSDate,

-240,

MONTH

)

VAR Running_Total_ASSETS =

CALCULATE(

SUM('Balance Sheet Information'[BS_$_Amount]),

'Balance Sheet Information'[AcctNum] < 2000,

REMOVEFILTERS('Calendar'),

KEEPFILTERS(MinDate),

USERELATIONSHIP('Calendar'[Date], 'Reference_Calendar'[Date])

)

VAR Running_Total_Liabilities_Equity_less_NetIncome_less_RetainedEarnings =

CALCULATE(

SUM('Balance Sheet Information'[BS_$_Amount]),

'Balance Sheet Information'[AcctNum] >= 2000,

'Balance Sheet Information'[AcctNum] <> 3500,

'Balance Sheet Information'[AcctNum] <> 3900,

REMOVEFILTERS('Calendar'),

KEEPFILTERS(MinDate),

USERELATIONSHIP('Calendar'[Date], 'Reference_Calendar'[Date])

)

VAR Test_Retained_earnings =

Running_Total_ASSETS -

YTD_Net_Income_Acct_3900 -

Running_Total_Liabilities_Equity_less_NetIncome_less_RetainedEarnings

RETURN

CALCULATE(

Test_Retained_earnings,

REMOVEFILTERS('Balance Sheet Information'),

REMOVEFILTERS('Calendar') -- Ensures same value across all rows

)

[learning_pbi_11]

I also tried removing filters from the 'reference_calendar' and that did not work either

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.