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 va...
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 

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