Forum Discussion
aar0n
Advocate II
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 datas...
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)
Zubair_Muhammad
Community Champion
Community ChampionHi 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] )
aar0nAdvocate II
Advocate IIafter testing it out, i ended up figuring out the month.
thanks for the great solution.. i used the following:
EOMONTH ( 'Table'[Last date in dataset], 'Table'[Value that represents the month] )
however, i need the value to change.. basically, what i'm getting is below... which means the "Value" column is just constant, while i need it to become continuously smaller.
| Type | Name | Date | Value |
| 1 | a | 2/28/2018 | Last known Value (for "Name" a) * 0.77^(1/12) |
| 1 | a | 3/31/2018 | Last known Value (for "Name" a) * 0.77^(1/12) |
| 1 | a | 4/30/2018 | Last known Value (for "Name" a) * 0.77^(1/12) |
| 1 | a | 5/31/2018 | Last known Value (for "Name" a) * 0.77^(1/12) |
| 1 | a | 6/30/2018 | Last known Value (for "Name" a) * 0.77^(1/12) |
| 1 | a | 7/31/2018 | Last known Value (for "Name" a) * 0.77^(1/12) |
| … | … | … | Last known Value (for "Name" a) * 0.77^(1/12) |
| 2 | b | 2/1/2018 | Last known Value (for "Name" b) * 0.77^(1/12) |
| 2 | b | 3/31/2018 | Last known Value (for "Name" b) * 0.77^(1/12) |
| … | … | … | Last known Value (for "Name" b) * 0.77^(1/12) |
| 2 | b | 1/31/2020 | Last known Value (for "Name" b) * 0.77^(1/12) |
what i need is
| Type | Name | Date | Value |
| 1 | a | 2/28/2018 | Last known Value (for "Name" a) * 0.77^(1/12) |
| 1 | a | 3/31/2018 | Value predicted above * 0.77^(1/12) |
| 1 | a | 4/30/2018 | Value predicted above * 0.77^(1/12) |
| 1 | a | 5/31/2018 | Value predicted above * 0.77^(1/12) |
| 1 | a | 6/30/2018 | Value predicted above * 0.77^(1/12) |
| 1 | a | 7/31/2018 | Value predicted above * 0.77^(1/12) |
| … | … | … | … |
| 2 | b | 2/1/2018 | Last known Value (for "Name" b) * 0.77^(1/12) |
| 2 | b | 3/31/2018 | Value predicted above * 0.77^(1/12) |
| … | … | … | … |
| 2 | b | 1/31/2020 | Value predicted above * 0.77^(1/12) |
- Ashish_Mathur
Super User
