Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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] )
Thank you very much. I have to spend some time to learn this formula but it look very interesting.
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] )
Thank you so much. I understand the formula and it will work.
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
Im sorry, I can not use any of them. It looks like there are no DAX-function that take care of depreciations.
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?
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.
Here is a link to the samplefile. I appreciate any tips:
https://1drv.ms/x/s!Aj_TBwnD6gaJgy6PE1llQB4nOkAC
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |