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] )

9 Replies

    • nankerp's avatar
      nankerp
      Helper III
      8 years ago

      Im sorry, I can not use any of them. It looks like there are no DAX-function that take care of depreciations.

      • Stachu's avatar
        Stachu
        Community Champion
        8 years ago

        my finance skills are a bit rusty, but isn't that just few simple equations? do you have sample data that you need to apply this to? can you share it?

    • 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] )