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

I send you this photo of my problem. Its very simple. I have an inputtabel (year, License, Capex ) and a parametertabel with information about amount of year depreciation which I use to divide capex. Then I need to distrubute depreciation over amount of years. In my model I also have a Calendertabel I connect to my Inputtabel.