Forum Discussion

JohannesM's avatar
JohannesM
Helper I
4 years ago
Solved

Generate table with years and calculated values

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.

IDLifespanActivation yearPurchase value
400502011500
40150197510 000
40250195012 000
40330201050 000
40440202040 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)

IDYearResidual value
40119700
40119710
40119720
40119730
40119740
401197510 000
40119769 800
40119779 600
40119789 400
40119799 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!

Need Help
  • JohannesM's avatar
    JohannesM
    4 years ago

    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 

7 Replies

  • sabilahmed's avatar
    sabilahmed
    Resolver I
    4 years ago

    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's avatar
      JohannesM
      Helper I
      4 years ago

      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's avatar
        sabilahmed
        Resolver I
        4 years ago

        Cheers mate.

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      JohannesM
      Helper I
      4 years ago

      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