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 ChampionHi 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
Community Champion
There is a 3rd argument of GenerateSeries,,,,(Interval) which allows you to give gap between dates
aar0nI 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] )