Forum Discussion
Advocate IIHow to create a Calculated Table for Future Values in from a Unique Name list
Hi guys,
I am looking to create a calculated table where the date column starts after a different query ends.
Here is a sample table of my Original dataset
| Type | Name | Last date in dataset | Last Known Value |
| 1 | a | 1/31/2017 | 10 |
| 2 | b | 1/31/2017 | 15 |
| 3 | c | 1/31/2017 | 12 |
| 4 | d | 1/31/2017 | 20 |
What i need, is to apply a formula for each "Name". The formula will take the last known "Value" for each unique "Name", and multiplies by 0.77^(1/12) for all future dates until 2020.
an example of what i'm looking for
| Type | Name | Date | Value |
| 1 | a | 2/1/2018 | Last known Value (for "Name" a) * 0.77^(1/12) |
| 1 | a | 2/2/2018 | Value predicted above * 0.77^(1/12) |
| 1 | a | 2/3/2018 | Value predicted above * 0.77^(1/12) |
| 1 | a | 2/4/2018 | Value predicted above * 0.77^(1/12) |
| 1 | a | 2/5/2018 | Value predicted above * 0.77^(1/12) |
| 1 | a | 2/6/2018 | Value predicted above * 0.77^(1/12) |
| … | … | … | … |
| 1 | a | 1/1/2020 | Value predicted above * 0.77^(1/12) |
| 2 | b | 2/1/2018 | Last known Value (for "Name" b) * 0.77^(1/12) |
| 2 | b | 2/2/2018 | Value predicted above * 0.77^(1/12) |
| … | … | … | … |
| 2 | b | 1/1/2020 | Value predicted above * 0.77^(1/12) |
The biggest issue for me isnt the formula.. ive just been struggling how to figure out how to build a date list for each unique name that starts after the original query ends.
Thanks a lot for the help,
Aaron
Hi aar0n
Try this Calculated Table
From the Modelling Tab>> New Table
New Table = ADDCOLUMNS ( GENERATE ( TableName, GENERATESERIES ( TableName[Last date in dataset] + 1, DATE ( 2020, 1, 1 ) ) ), "myvalue", TableName[Last Known Value ] * .77 ^ ( 1 / 12 ) )Hi aar0n
In that case, create a New Table which will give you Month Numbers
Table = GENERATE ( TableName, GENERATESERIES ( 1, DATEDIFF ( TableName[Last date in dataset], DATE ( 2020, 1, 1 ), MONTH ) ) )Then you can add a calculated column to get Month End Dates
Column = EOMONTH ( 'Table'[Last date in dataset], 'Table'[Value] )
- HI
From your last post I noticed this. So I think adjusting the POWER by -1 should fix it.
My VALUE =
'Table'[Last Known Value ]
* ( 0.77
^ ( 1 / 12 ) )
^ ('Table'[Value that represents the month]-1)
19 Replies
- Zubair_Muhammad
Community Champion
Hi aar0n
Try this Calculated Table
From the Modelling Tab>> New Table
New Table = ADDCOLUMNS ( GENERATE ( TableName, GENERATESERIES ( TableName[Last date in dataset] + 1, DATE ( 2020, 1, 1 ) ) ), "myvalue", TableName[Last Known Value ] * .77 ^ ( 1 / 12 ) )
aar0nThat worked amazing! thank you so much!
would you be able to explain how youre using the Addcolumns, generate, and Generateseries together?
for future reference (i'm definitely going to be using this again) i am also wondering how you would do the same process, except the dates increase by 1 month instead of 1 day
- Zubair_Muhammad
Hi aar0n
Basically You have to work backwards.
GenerateSeries creates Table of Dates you need
Generate crossjoins it with each row of the table
Then you add a column to this table using AddColumns