Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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
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
)
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.
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.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
11 | |
10 | |
9 | |
8 |