Forum Discussion
Advocate IIHow to create a Calculated Table for Future Values in from a Unique Name list
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)
Community Champion
There is a 3rd argument of GenerateSeries,,,,(Interval) which allows you to give gap between dates
Advocate III found that, but how do you set it to generate the last day of each month? i figured out how to add a constant value, just not sure how to say last day in month
- Zubair_Muhammad
- Zubair_Muhammad
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] )
- aar0n
after 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)