Calculate Depreciation for Financial Year Ended in June

[Anonymous]

Hi PBI Gurus,

I wonder if anyone can help me on this.

I am trying to calculate a YTD (based on financial year ended 30/6) depreciation for assets however I am struggling to find solutions.

Please see the screenshot of the Excel file below. The actual data is a lot bigger than this. Just wonder if I want to get a YTD depreciation for all assets up until 30/6/2019. Ultimately I would like to see a column to show me the YTD depreciation for each asset. For example for below asset, the depreciation of it for Financial Year 19 up until 30/6/19 would be $120,000/10 years/12 months*8 months =$8,000. 

Could someone please have a look on this?

Cheers

Jimmy

[Ashish_Mathur]

Hi,

While the calculation is not difficult, I am not clear about the layout of your expected result.  That one asset has a life month of 120.  Should shouldn't there by 120 rows created (one for each month)?  I suggest that you take an example of 2 assets with different life months and show the exact result (both layout and figures) that you are expecting.

[Anonymous]

Hi Ashish Mathur,

Thank you for your reply.

I might have confused you, I just would like to have a column like below in red to work in Power BI, to calculate the yearly depreciation cost from 1/7/2018 - 30/6/2019 or the date they require to 30/6/2019, if they are acquired during the 2019 financial year.

[Ashish_Mathur]

Hi,

Please recheck all start and end dates - i think there is some problem in the second one.

[Anonymous]

Oops sorry please corrected as follows.

[Ashish_Mathur]

Check t he figures in the Life in months column.  They are still wrong.  It really cannot be so difficult to cross check all information before you hit the send button!!!!!!

[Anonymous]

Sorry now it should be right!

[Ashish_Mathur]

Hi,

You may download my solution file from here.

Hope this helps.

[Anonymous]

Hi,

I just looked at your file and found its slightly different from mine because I only had one amount which is the original cost of the asset whereas yours has the date seperated by months already, therefore I can't use your method.

Would you happen to know any other ways to work this around?

Cheers

Jimmy

[Ashish_Mathur]

Hi,

In my solution, it is not already there.  I have applied transformations in the Query Editor to create months from your inout data.  Click on Home > Edit Queries to see the transformation steps i have applied to your data.

[amitchandak]

Datesytd and total ytd can help. Make sure you have date calendar joined to your date.

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"06/30"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"06/30"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"06/30"))

 To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin