Divide value in a period (asset depreciation)

[JorgeMolano]

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!

[v-cgao-msft]

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(
    SELECTEDVALUE('Add_years_expiation_date'[Year_Expiration_Extension]),
    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