์Need help Cohort method dax error when filter

[Yutthakorn]

I want to find Repay and Revisited

Customer Retention % = 

Var CurrentMonthAfter = SELECTEDVALUE('Month After'[Value])

Var CurrentFirstOrderMonth = SELECTEDVALUE(SummaryBilling[First Order Date])

Return

DIVIDE(

        CALCULATE(

            DISTINCTCOUNT(SummaryBilling[ChtNum]),

            FILTER( SummaryBilling, EOMONTH(SummaryBilling[DateVisit],0)= EOMONTH(CurrentFirstOrderMonth,CurrentMonthAfter) && SummaryBilling[DateVisit].[Year] = YEAR(CurrentFirstOrderMonth)

            )

    ),

 DISTINCTCOUNT(SummaryBilling[ChtNum])

)

But When Filter By Category i have problem

Cht Num is  Index

 I'm not sure where I went wrong.

[v-yangliu-msft]

Hi  @Yutthakorn ,

You can try adding the ALL() function to it.

CALCULATE(
            DISTINCTCOUNT(SummaryBilling[ChtNum]),
            FILTER( All('SummaryBilling'), EOMONTH(SummaryBilling[DateVisit],0)= EOMONTH(CurrentFirstOrderMonth,CurrentMonthAfter) && SummaryBilling[DateVisit].[Year] = YEAR(CurrentFirstOrderMonth)
            )

ALL function (DAX) - DAX | Microsoft Learn

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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