cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Divide value in a period (asset depreciation)

Hello community,

I've been researching this topic extensively, but I haven't been able to find a solution yet.

Let's imagine I have a list of assets in a company, such as desks, laptops, chairs, and printers. Each of these assets has a replacement date and an associated replacement cost.

However, it's possible that some assets may function for longer than initially expected, meaning their expiration date extends. Therefore, I need to reserve the depreciation amount until the new expiration date of each asset.

Here are the tables I have in the model:

The tables Fact_Assets and Dim_Calendar are related by Fact_Assets[Expiration_Date] to Dim_Calendar[Date]. Add_years_expiation_date is a variable that will be selected through a dropdown list within the report.

I want to create a measure that divide the sum of "Replacement_Cost" by the value of "Years_Expiration_Extension" so that the result is displayed for each of the following years.

For example, if I have a "Replacement Cost" of 1000 in the year 2022 and the "Years of Expiration Extension" is 4, it should be displayed as follows:

• 2022: 250
• 2023: 250
• 2024: 250
• 2025: 250

I appreciate any help or suggestions you can provide. Thank you in advance!

Community Support

Hello,

Please create these calculated column first:

``````Replacement_Cost_5 =
VAR __year2 = 'Dim_Calendar'[Year]
VAR __year1 = 'Dim_Calendar'[Year] - 4
VAR __result = DIVIDE(SUMX(FILTER(ALL('Dim_Calendar'), 'Dim_Calendar'[Year] >= __year1 && 'Dim_Calendar'[Year] <= __year2),[Total replacement cost]),5)
RETURN
__result``````

and then please create a new measure:

``````Measure_rep_cost =
SWITCH(
1,SUM('Dim_Calendar'[Replacement_Cost_1]),
2,SUM('Dim_Calendar'[Replacement_Cost_2]),
3,SUM('Dim_Calendar'[Replacement_Cost_3]),
4,SUM('Dim_Calendar'[Replacement_Cost_4]),
5,SUM('Dim_Calendar'[Replacement_Cost_5])
)``````

Output:

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.