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: ID Date Customers C...
churn
churn rate
DAX
Excel
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
    )