DB 05-09-2020 21:42 PM

[Greg_Deckler]

In my recent quest to create or catalog as many DAX equivalents for Excel functions, because DB involves recursion, there is no "clean" DAX solution, it's ugly and you have to use reasonable maximum. 😞

DB = 
    VAR __Cost = [Cost]
    VAR __Salvage = [Salvage]
    VAR __Life = [Life]
    VAR __Period = MAX('Table'[Year])
    VAR __Month = IF(ISBLANK([Month]),12,[Month])
    VAR __Rate = ROUND(1 - POWER( __Salvage / __Cost,(1/__Life)),3)
    VAR __Table1 = { (1, __Cost * __Rate * __Month/12) }
    VAR __Table2 = { (2, (__Cost - SUMX(__Table1,[Value2])) * __Rate) }
    VAR __Table3 = { (3, (__Cost - SUMX(UNION(__Table2,__Table1),[Value2])) * __Rate) }
    VAR __Table4 = { (4, (__Cost - SUMX(UNION(__Table3,__Table2,__Table1),[Value2])) * __Rate) }
    VAR __Table5 = { (5, (__Cost - SUMX(UNION(__Table4,__Table3,__Table2,__Table1),[Value2])) * __Rate) }
    VAR __Table6 = { (6, (__Cost - SUMX(UNION(__Table5,__Table4,__Table3,__Table2,__Table1),[Value2])) * __Rate) }
    VAR __Table7 = { (7, (__Cost - SUMX(UNION(__Table6,__Table5,__Table4,__Table3,__Table2,__Table1),[Value2])) * __Rate) }
    VAR __Table8 = { (8, (__Cost - SUMX(UNION(__Table7,__Table6,__Table5,__Table4,__Table3,__Table2,__Table1),[Value2])) * __Rate) }
    VAR __Table9 = { (9, (__Cost - SUMX(UNION(__Table8,__Table7,__Table6,__Table5,__Table4,__Table3,__Table2,__Table1),[Value2])) * __Rate) }
    VAR __Table10 = { (10, (__Cost - SUMX(UNION(__Table9,__Table8,__Table7,__Table6,__Table5,__Table4,__Table3,__Table2,__Table1),[Value2])) * __Rate) }
    VAR __Table = UNION(__Table10, __Table9,__Table8,__Table7,__Table6,__Table5,__Table4,__Table3,__Table2,__Table1)
RETURN
    SWITCH(
        TRUE(),
        __Period <> __Life + 1,MAXX(FILTER(__Table,[Value1] = __Period),[Value2]),
        __Period = __Life + 1,( (__Cost - SUMX(FILTER(__Table, [Value1] < __Period),[Value2]) ) * __Rate * (12-__Month)) / 12
    )

eyJrIjoiNWMwNWUyNmYtYWI3Mi00ZGZmLTg5ZTYtZDY1N2I3ZDNmYmRlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9