dax measure with ALL( DatesTable) does not ignore date filter

[Dom_Smith825]

Hi Folks,

I want to calculate:
AccountsReceivable = CALCULATE( SUMX( FACT_Actuals_Master, (FACT_Actuals_Master[Credits] - FACT_Actuals_Master[Debits]) )

I also need to add the filter FILTER( FACT_Actuals_Master, [Account Master Name] = "Accounts Receivable")

I also want to ignore any filtering coming from the relationship Datestable[DATE] = FACT_Actuals_Master[Date]

I have: AccountsReceivable =  CALCULATE( SUMX( FACT_Actuals_Master, (FACT_Actuals_Master[Credits] - FACT_Actuals_Master[Debits]) ),
FILTER( FACT_Actuals_Master, [Account Master Name] = "Accounts Receivable"),
ALL(DatesTable) )

There is a DatesTable[MonthYear] in the filters pane acting on all pages, when it is used, the AccountsReceivable total changes, but is should remain constant.

Any thoughts/pointers?

Thanks

[ppm1 · Accepted solution]

Please try this version.

Converted_AR2 =
VAR SelectedRate =
    SWITCH (
        TRUE (),
        [SelectedCurrency] = "CAD", 1,
        [SelectedCurrency] = "EUR", P_EUR_to_CAD_Rate[EUR_to_CAD_Rate_Value],
        [SelectedCurrency] = "INR", DIVIDE ( 1, P_INR_to_CAD_Rate[INR_to_CAD_Rate_Value] ),
        [SelectedCurrency] = "USD", P_USD_to_CAD_Rate[USD_to_CAD_Rate_Value],
        BLANK ()
    )
VAR ExchangeRate =
    IF (
        MAX ( DatesTable[Date] ) > Labels[Ledger_LatestDate],
        SelectedRate,
        [Exchange_Rate_Normalised]
    )
VAR AccountsReceivable =
    CALCULATE (
        SUMX (
            FACT_Actuals_Master,
            ( FACT_Actuals_Master[Credits] - FACT_Actuals_Master[Debits] )
        ),
        FACT_Actuals_Master[Account Master Name] = "Accounts Receivable",
        ALL ( DatesTable )
    )
RETURN
    AccountsReceivable * ExchangeRate * -1

Pat

[Dom_Smith825]

@ppm1  Thanks ppm1, that works perfectly for me.

[Dom_Smith825]

@Greg_Deckler I think I've shared a sample database with you here: https://www.dropbox.com/s/lhmjui5bmkgsri2/AR%20Database.pbix?dl=0

New to this, may not have done this properly.

[Dom_Smith825]

@Greg_Deckler, thanks, I'll take a look at that and if I can't see anything obvious I'll set up a pbix replicating the problem.

[Greg_Deckler]

@Dom_Smith825 It's hard to be certain what is going on here. Sometimes these kinds of issues are caused by the Auto-exist "feature".