combine filter doesn't work (with parameters)

[DPIT_FR]

Hello all,

I tried to filter a table with two filters, but it doesn't work (returns blank lines except for a curious total).

My objective is to enable users to filter based on a threshold (with parameter) and calculate it for the previous year. it's ok for the current year, but not for Y-1

I have two measures that work individually:

First: (filter line)

Nb Passage Horaire (Compteurs EXCLUS) =

CALCULATE(sum(Passage[NB_PASSAGE_HORAIRE_ESTIME]),

    FILTER(

        Passage,

        Passage[NB_PASSAGE_HORAIRE_ESTIME] < 'ParamètreExclusion'[ParamCapteurDouteuxRetour]

    )

)

Second : (filtre Y-1)

Nb Passages Horaire (A-1) =

CALCULATE(

    sum(Passage[NB_PASSAGE_HORAIRE_ESTIME]),

    Dateadd(Calendrier[Date],-1,YEAR)

)

However, when I try to combine both, it doesn't work. I've tried various approaches, including TREATAS, but nothing seems to work 😔.

Here is one combined measure i tried

Nb Passage Horaire A-1 (Compteurs EXCLUS) =
CALCULATE(
SUM(Passage[NB_PASSAGE_HORAIRE_ESTIME]),
DATEADD(Calendrier[Date], -1, YEAR),
FILTER(
Passage,
Passage[NB_PASSAGE_HORAIRE_ESTIME] < 'ParamètreExclusion'[ParamCapteurDouteuxRetour]
)
)

if you can explain it...

Thanks a lot

[DPIT_FR]

hello @freginier , @Anonymous 

Thanks a lot for your help. Unfortunately, I can't get the result... and I don't understand how I get this result....

Unfortunately, I wasted too much time on this problem, I will  see with my client if it is not possible to remove this functionality...

However, if you see another possibility, I will take note of it. (I put the formulas below). In any case, once again I thank you for the time spent.

Nb Passage Horaire (Compteurs EXCLUS) =

CALCULATE(sum(Passage[NB_PASSAGE_HORAIRE_ESTIME]),

    FILTER(

        Passage,

        Passage[NB_PASSAGE_HORAIRE_ESTIME] < parameter[Parameter_value]

    )

)

TestYearBefore1 =

CALCULATE(

   SUMX(         FILTER(         Passage,         Passage[NB_PASSAGE_HORAIRE_ESTIME] < parameter[Parameter_value] && DATEADD(Calendrier[Date], -1, YEAR)         ),Passage[NB_PASSAGE_HORAIRE_ESTIME]     ) )

TEstYearBefore2 =

CALCULATE (

    SUMX (

        FILTER ( 'Passage', 'Passage'[NB_PASSAGE_HORAIRE_ESTIME] > parameter[Parameter_value] ),

        [NB_PASSAGE_HORAIRE_ESTIME]

    ),

    DATEADD ( 'Calendrier'[Date], -1, YEAR )

)

one more time :thank you much

[Anonymous]

Hi @freginier ,Thanks for the quick reply, I'll add further.

Hi @DPIT_FR ,

The Table data is shown below:

Regarding your question, please modify your measure according to the expression I provided.

MEASURE =
CALCULATE (
    SUMX (
        FILTER ( 'Table', 'Table'[Value] > 'Parameter'[Parameter Value] ),
        [Value]
    ),
    DATEADD ( 'Calendar'[Date], -1, YEAR )
)

Final output

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

[freginier]

Hello @DPIT_FR  I would like to help you could you please share with me a power bi file with fake / anonymize data ? send it to me in private 

[DPIT_FR]

thanks a lot. i sent you a MP