Forum Discussion
nankerp
Helper III
Helper IIILinear 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?
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
- Stachu
Community Champion
nankerpIm 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?
- TomMartens
Super User
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
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] )
