Forum Discussion

nankerp's avatar
nankerp
Helper III
8 years ago
Solved

Linear Depreciation - DAX

Im looking for a formula that can calculate linear depreciation (Capex divided years).   Are there any who have tips about making formula in DAX that can manage this?
Need Help
Tips and Tricks
  • Stachu's avatar
    Stachu
    8 years ago

    based on the originals tables you posted this should work, I'm assuming second table is named Parameter

    Depreciation =
VAR License = Inputtabel[License]
VAR CurrentYear = Inputtabel[Year]
VAR NrOfDeprYears =
    RELATED ( Parameter[Year depreciation] )
VAR CapexToDate =
    ADDCOLUMNS (
        FILTER (
            Inputtabel,
            Inputtabel[License] = License
                && Inputtabel[Year] <= CurrentYear
                && NOT ( ISBLANK ( Inputtabel[Capex] ) )
        ),
        "NrOfYearsDepr", RELATED ( Parameter[Year depreciation] )
    )
VAR FullCapexPerYear =
    FILTER (
        GENERATE (
            CapexToDate,
            GENERATESERIES (
                [Year],
                [Year] + RELATED ( Parameter[Year depreciation] )
                    - 1,
                1
            )
        ),
        [Value] = CurrentYear
    )
RETURN
    SUMX ( FullCapexPerYear, [Capex] / [NrOfYearsDepr] )
nankerp's avatar
nankerp
Helper III
8 years ago

Thank you very much. I have to spend some time to learn this formula but it look very interesting.

Stachu's avatar
Stachu
Community Champion
8 years ago

based on the originals tables you posted this should work, I'm assuming second table is named Parameter

Depreciation =
VAR License = Inputtabel[License]
VAR CurrentYear = Inputtabel[Year]
VAR NrOfDeprYears =
    RELATED ( Parameter[Year depreciation] )
VAR CapexToDate =
    ADDCOLUMNS (
        FILTER (
            Inputtabel,
            Inputtabel[License] = License
                && Inputtabel[Year] <= CurrentYear
                && NOT ( ISBLANK ( Inputtabel[Capex] ) )
        ),
        "NrOfYearsDepr", RELATED ( Parameter[Year depreciation] )
    )
VAR FullCapexPerYear =
    FILTER (
        GENERATE (
            CapexToDate,
            GENERATESERIES (
                [Year],
                [Year] + RELATED ( Parameter[Year depreciation] )
                    - 1,
                1
            )
        ),
        [Value] = CurrentYear
    )
RETURN
    SUMX ( FullCapexPerYear, [Capex] / [NrOfYearsDepr] )
  • nankerp's avatar
    nankerp
    Helper III
    8 years ago

    Thank you so much. I understand the formula and it will work.