cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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?

1 ACCEPTED SOLUTION
Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

9 REPLIES 9
Super User II
Super User II

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


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

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

Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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

Community Champion
Community Champion

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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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.

 

Depreciation - Problem.png

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

 

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

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors