Remove filter nor removing filters

[Sibrulotte]

Hi, 

I've joing a basic model that ressembles something that work great for a topic we have. 

I'd like to show the number of customers that were present on may 31st of the previous fiscal year of the selection done. For exemple, in the file, fiscal year 2025 is selected, and I should see 6 customers in my measure:

NB_epargnant_31_mai_comp =

var vdern_date_31_mai_prec = date(max(Calendrier[Fiscal year]), 05,31)-365

var vfiltre_31mai = filter(Soldes, Soldes[Date_solde] = vdern_date_31_mai_prec)

var result = calculate(distinctcount(Soldes[Epargnant_ID]), removefilters(Calendrier) , vfiltre_31mai)

return

result

Unfortunatly, the measure returns nothing as it's filtering for 2025 results.

https://fondsftq.sharefile.com/public/share/web-s9ff08a74cc09414391732c456979d779

[lbendlin]

the number of customers that were present on may 31st of the previous fiscal year of 

what does "present"  mean?  They had saldos?

[Sibrulotte]

Wow, you really did me dirty exposing my fake client names lol.

Yes, it's a count of Epargnant_ID. and Epargnant can have multiple accounts, but even if this model doesn't represent that reality, yes it's still a distinct customer count.

[Anonymous]

Hi @Sibrulotte ,

Please refer to below code.

NB_epargnant_31_mai_comp = 
var vdern_date_31_mai_prec = date(max(Calendrier[Fiscal year]), 05,31)-365
var result = calculate(distinctcount(Soldes[Epargnant_ID]), FILTER(ALL(Soldes),Soldes[Date_solde] = vdern_date_31_mai_prec))
var result1 = calculate(distinctcount(Soldes[Epargnant_ID]), REMOVEFILTERS(Soldes),Soldes[Date_solde] = vdern_date_31_mai_prec)
return
result1

If you want to use REMOVEFILTERS() you can try result1. You can also try ALL() function like result, the result is the same.

Best Regards,
Rico Zhou

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

[Sibrulotte]

Hi Zhou, thank you for the response. The result works partially since the measure does not provide a ventilated result when using in a matrix with a reference table like "Régime" in this file. All values of régime come out with 6, but I was expecting 3 and 3.

[Anonymous · Accepted solution]

Hi @Sibrulotte ,

I update the measure and you can try this.

NB_epargnant_31_mai_comp =
VAR vdern_date_31_mai_prec =
    DATE ( MAX ( Calendrier[Fiscal year] ), 05, 31 ) - 365
VAR result =
    CALCULATE (
        DISTINCTCOUNT ( Soldes[Epargnant_ID] ),
        FILTER (
            ALLEXCEPT ( Soldes, Comptes_epargnants, 'Régimes' ),
            Soldes[Date_solde] = vdern_date_31_mai_prec
        )
    )
RETURN
    result

Result is as below.

Best Regards,
Rico Zhou

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