The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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
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
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
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 🙂
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 😃
Cheers mate.
Just to understand better, do you have 2 Year columns? One for Activation year and one for ....?
So am I rigth in saying (from your formula):
C = Activation Year
Year = Current Year?
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |