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

Calculating Depreciation in DAX

I'm having a rough time doing something fairly simple, or at least I'd think it was simple. 

I simply need to see the value of products reduce monthly in a pivot table. I want to see that value reduce until it hits 0. I have different products and product families that I want as my rows on a pivot table and the dates as the columns but I can't seem to figure this out for whatever reason.

 

Can someone provide some general DAX help for this? My depreciation is linear and essentially based on "Value - (Residual Value/Lifespan)

 

Let me know if more information is needed. I feel dumb asking this as it should be a simple fix.

 

7 REPLIES 7
seanpratt
Helper III
Helper III

Thanks so much to all who checked in. I actually solved the answer by scouring the internet. Turns out it was turning those basic tables into a fleshed out table through PowerQuery and then it was pretty quick with a couple of DAX formulas after.

 

This wasn't the exact situation but the same logic applies. Leaving this link here in case anyone ever needs it!

 

https://forum.enterprisedna.co/t/create-amortization-table/2404

Hi,

The link can not be openned.

Could you pls. share some details on the logic and dax formula?

Thanks.

fhill
Super User I
Super User I

Is this what you had in mind?   If so, i'll add more details, I took a table of Description, Start Month, & # of Month to LIST all months per Item, and calculate the remaining value each month until them item zeros out.  

 

image.png



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




Hello, I would like to see your solution if possible? I have the same issue, but also trying to figure out one more problem on top of the depriciation, which is how much is the depreciation in the last year before going to 0.

 

Kind regards,
Vlad

cstaulbee
Resolver III
Resolver III

Are you able to share your table? Does your table contain the depreciation amount, or are you hoping for the DAX to calculate it for you?

Annotation 2020-02-04 081847.png

 

Here it is. Setting the depreciation value as standard value - ((1-resid.)/(useful)

 

I've been then using the depreciation value and dividing by 12 months to get a monthly total but not sure if I should be doing that.

seanpratt
Helper III
Helper III

Bump.

Any help on this, please? Been trying variations all day. I basically just need to show a repeating deprecation value between (Start Date) and (End Date).

Please help

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.