Linear Depreciation - DAX

[nankerp]

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?

[TomMartens]

Hey,

I think that you can adapt the solution that is described here:

https://www.minceddata.info/2018/02/21/using-table-iterators-to-calculate-a-future-value/

to your need.

Regards,
Tom

[nankerp]

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

[Stachu · Accepted solution]

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]

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

[Stachu]

maybe one of these?
https://msdn.microsoft.com/en-us/query-bi/dax/xnpv-function-dax
https://msdn.microsoft.com/en-us/query-bi/dax/xirr-function-dax

[nankerp]

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

[Stachu]

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]

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.

[nankerp]

Here is a link to the samplefile. I appreciate any tips:

https://1drv.ms/x/s!Aj_TBwnD6gaJgy6PE1llQB4nOkAC