I am having trouble creating a measure requirement from the client that calculates their yearly churn rate.
Excel formula and expected output Cumulative:
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.
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.
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.
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.