Forum Discussion
Countrows inside calculate with filters
Please try this expression. Note that I modified your FILTER parts to use better practice (just filter the column(s) you need, not the whole table).
_Churn MtNet =
VAR minDate =
MIN ( 'Contrats_Date'[Date] )
VAR maxDate =
MAX ( 'Contrats_Date'[Date] )
VAR FirstValue =
CALCULATE (
SUM ( Contrats_Fact[MtNet] ) * -1,
FILTER (
ALL ( Contrats_Ent[ContDtResil] ),
Contrats_Ent[ContDtResil] <= maxDate
&& Contrats_Ent[ContDtResil] >= minDate
),
FILTER (
ALL ( Contrats_Fact[PromoDeb] ),
Contrats_Fact[PromoDeb] <= maxDate
&& Contrats_Fact[PromoFin] >= maxDate
)
)
VAR SecondValue =
CALCULATE (
COUNT ( Contrats_Ent[SiteNum] ),
FILTER (
ALL ( Contrats_Ent[ContDtResil] ),
ISBLANK ( Contrats_Ent[ContDtResil] )
|| ( Contrats_Ent[ContDtResil] < minDate
&& Contrats_Ent[ContDtResil] > maxDate )
)
)
RETURN
DIVIDE ( FirstValue, SecondValue )
Pat
Hello
Thanks. I found another way to work it out.