Converting Excel formula to DAX

[syncinatti]

Hello all.

I am having trouble creating a measure requirement from the client that calculates their yearly churn rate.

Excel formula and expected output Cumulative:

ID	Date	Customers	Churn rate	Cumulative	Formula
C	2018	52	0.99	51.48	=C2*D2
C	2019	182	0.98	228.8104	=(C3+E2)*D3
C	2020	178	0.95	386.46988	=(C4+E3)*D4
C	2021	252	0.98	625.700482	=(C5+E4)*D5
C	2022	302	0.94	872.038453	=(C6+E5)*D6

Currently, this is my measure:

#Cumulative =
CALCULATE (
    SUM ( [Customers] ) * SUM ( [Churn Rate] ),
    FILTER (
        ALL ( 'Table' ),
        Table[ID] = MAX ( Table[ID] )
            && Table[Year] <= MAX ( Table[Year] )
    )
)

Is it possible to recreate the requirement dynamically? I'm at wits' end on understanding the logic flow.

Thank you.

syncinatti

[tamerj1]

Hi @syncinatti 
Please refer to attached file with the solution

Cumulative = 
VAR CurrentDate = SELECTEDVALUE ( 'Table'[Date] )
VAR CurrentIDTable = 
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) )
VAR T1 = FILTER ( CurrentIDTable, 'Table'[Date] <= CurrentDate )
RETURN
    SUMX ( 
        T1,
        VAR CurrentDate1 = 'Table'[Date]
        VAR Customers = 'Table'[Customers]
        VAR T2 = FILTER ( T1, 'Table'[Date] >= CurrentDate1 )
        VAR ChurnRate = PRODUCTX ( T2, [Churn rate] )
        RETURN
            Customers * ChurnRate
    )

[v-binbinyu-msft]

Hi @syncinatti ,

I'm sorry that DAX can't support recursive operation so far. You can choose  calculate it in Excel and then import it into  Power BI Desktop.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EylesIT]

@syncinatti , this involves recursion. I'm not sure that's possible in DAX.

[syncinatti]

Now that's the word I'm looking for, "recursive" formula. This is essentially impossible on my level, and can be made only through a bloody workaround.

[tamerj1]

@syncinatti 

Why not possible?