Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |