Forum Discussion

syncinatti's avatar
syncinatti
New Member
4 years ago

Converting Excel formula to DAX

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:

IDDateCustomersChurn rateCumulativeFormula
C2018520.9951.48=C2*D2
C20191820.98228.8104=(C3+E2)*D3
C20201780.95386.46988=(C4+E3)*D4
C20212520.98625.700482=(C5+E4)*D5
C20223020.94872.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

churn
churn rate
DAX
Excel

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

    • syncinatti's avatar
      syncinatti
      New Member
      4 years ago

      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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.