topic Converting Excel formula to DAX in DAX Commands and Tips

Converting Excel formula to DAX

syncinatti — Mon, 25 Jul 2022 08:29:12 GMT

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

Re: Converting Excel formula to DAX

Anonymous — Mon, 25 Jul 2022 09:58:57 GMT

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

Re: Converting Excel formula to DAX

syncinatti — Mon, 25 Jul 2022 12:39:08 GMT

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.

Re: Converting Excel formula to DAX

Anonymous — Fri, 29 Jul 2022 09:56:17 GMT

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.

Re: Converting Excel formula to DAX

tamerj1 — Sun, 18 Sep 2022 18:54:34 GMT

syncinatti

Why not possible?

Re: Converting Excel formula to DAX

tamerj1 — Sun, 25 Sep 2022 15:45:31 GMT

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 )