Replacing ranked values with values from another table over time.

[emkane]

Hello Everyone,

I am not sure if this can be done, but I am trying to show the median age of the units at a future date knowing that some units will be replaced over time. My data looks like the example below, the age is calculated from the build date. The units to be demoed should be the oldest units at the time. I suspect I need to rank the age of the units and then remove the top n values (number to be demoed) and then add the build units with the year as the build date. I also suspect I will need to create a table representing each unit to build with a new unit ID and year built date.

Thanks for the help.

[v-eachen-msft]

Hi @emkane ,

I probably understand what you mean. You want to remove the oldest ones ( demo ) and add new ones ( build ) based on the year. The new ones' ages start form 0, right? But how to define the new UnitID?

[emkane]

Hi @v-eachen-msft ,

Correct, 

In 2025 Area-1 loses 2 of the 60-year-old units and gains 2 new units, Area-3 loses 5 80-year-old units and gains 5. Age would be calculated with a DateDiff function. 

I am thinking my Change table should really be two tables, a demo table and a new unit table that looks like:

DemoDate     Location     UnitsToDemo

1/1/2025        Area-1        2

1/1/2025        Area-3        4

BuildDate    UnitID
1/1/2025     NewArea1-1

1/1/2025     NewArea1-2

1/1/2025     NewArea3-1

1/1/2025     NewArea3-2

1/1/2025     NewArea3-3

1/1/2025     NewArea3-4