Generate table with years and calculated values

[JohannesM]

I have a table of items with column A containing item ID, column B containing the items lifespan, column C containing the year each table became active, and lastly column D with the items purchase value.

ID	Lifespan	Activation year	Purchase value
400	50	2011	500
401	50	1975	10 000
402	50	1950	12 000
403	30	2010	50 000
404	40	2020	40 000

I want to generate a table with calculated residual value for each item and year in between 1970 and 2070. The depreciation rate is linear so the residual value would be calculated by something like D - (Year - C) * (D / B)

ID	Year	Residual value
401	1970	0
401	1971	0
401	1972	0
401	1973	0
401	1974	0
401	1975	10 000
401	1976	9 800
401	1977	9 600
401	1978	9 400
401	1979	9 200

I could of course create this table in Excel and import but 1. I would like this to be easy to update and 2. there will eventually be millions of rows. How would I best go about this?

Would greatly appreciate any help, thanks!

[v-shex-msft]

Hi @JohannesM,

You can try to use crossjoin function to generate the table with ID and year, then use addcolumns function to add a custom field to lookup values from the raw table records.

NewTable =
VAR _yearList =
    VALUES ( 'Table'[Activation year] )
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CROSSJOIN (
                VALUES ( 'Table'[ID] ),
                GENERATESERIES (
                    MINX ( _yearList, [Activation year] ),
                    MAXX ( _yearList, [Activation year] ),
                    1
                )
            ),
            "Residual value",
                VAR total =
                    CALCULATE (
                        MAX ( 'Table'[Purchase value] ),
                        FILTER (
                            ALLSELECTED ( 'Table' ),
                            [Activation year] <= [Value]
                                && [ID] = EARLIER ( 'Table'[ID] )
                        )
                    )
                VAR activeYear =
                    CALCULATE (
                        MAX ( 'Table'[Activation year] ),
                        FILTER ( ALLSELECTED ( 'Table' ), [ID] = EARLIER ( 'Table'[ID] ) )
                    )
                VAR currYear = [Value]
                VAR lifespan =
                    CALCULATE (
                        MAX ( 'Table'[Lifespan] ),
                        FILTER ( ALLSELECTED ( 'Table' ), [ID] = EARLIER ( 'Table'[ID] ) )
                    )
                VAR offset = total / lifespan
                VAR diff = currYear - activeYear
                RETURN
                    IF ( currYear >= activeYear, MAX ( total - diff * offset, 0 ), 0 )
        ),
        "ID", [ID],
        "Year", [Value],
        "Residual value", [Residual value]
    )

Regards,

Xiaoxin Sheng

[JohannesM · Accepted solution]

Hi Xiaoxin,
I actually ended up doing something close to that. I first duplicated the table I needed and then removed some columns to get the leanest table possible. I then created a new table with one column of years from 1970 to 2070 and then simply generated a new table with generate(duplicatedTable, newTable) to get a table with IDs and years. 
Only thing left was to create a if-statement to calculate the value I needed. It is a table with many rows but it doesn't seem to be a problem using if-logic so that's great.
Mabye I will try your solution if mine runs in to issues when we add even more rows to the modell, so thanks 😃

Br
Johannes 

[sabilahmed]

The formula you mentioned could be replicated in DAX:

D - (Year - C) * (D / B)

Question: What is "Year" in the formula?

If you are not comfortable with DAX, could you share  as screeshot of the columns?

Thanks 🙂

[JohannesM]

Edited my post to include mock-up of the table I have and the table I want to create. I hope it is easier to understand now 😃

[sabilahmed]

Cheers mate.

Just to understand better, do you have 2 Year columns? One for Activation year and one for ....?

[sabilahmed]

So am I rigth in saying (from your formula):

C = Activation Year

Year = Current Year?

[JohannesM]

Activation Year (C) is the year that the item became active yes.

Year (column B in the new table). I want to show the residual value for all years between 1970 and 2070 for each item. So "Year" is just a list of all years between those dates.