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)
Advocate IIhi ashish,
thank you for the contribution, but your solution has the same issue as the other solution, as shown by my example above.. the 'value' should be constantly decreasing towards 0, but in both solutions here, the values are a constant value for each date
Super UserHi,
When you click on Data icon on the left hand side pane, you will observe that the Dates increment daywise. is that correct or should they be incrementing month wise?
- Zubair_Muhammad
Community Champion
To get the decreasing values, please add this calculated column to the calculated table you created
My VALUE = 'Table'[Last Known Value ] * ( 0.77 ^ ( 1 / 12 ) ) ^ 'Table'[Value that represents the month] - aar0n
Zubair_Muhammad that will not give me the correct value, since the calculation is being taken to the exponent of the month number.
it is really close, but below is a sample of what is happening, and what i'm looking for..
Type Date Last known Value * (0.77^(1/12))^Value that represents month Last known Value * (0.77^(1/12)) Last known value 1/31/2017 29.69 29.69 calculated 2/28/2018 28.4243127 29.05033142 calculated 3/31/2018 27.81191325 28.42444444 calculated 4/30/2018 27.21270789 27.81204215 calculated 5/31/2018 26.62641236 27.21283401 calculated 6/30/2018 26.05274852 26.62653577 calculated 7/31/2018 25.49144422 26.05286927 calculated 8/31/2018 24.94223317 25.49156236 calculated 9/30/2018 24.40485483 24.94234877 calculated 10/31/2018 23.87905426 24.40496794 calculated 11/30/2018 23.36458202 23.87916493 calculated 12/31/2018 22.86119405 23.36469031 calculated 1/31/2019 22.36865152 22.8613 calculated 2/28/2019 21.88672078 22.36875519 - Zubair_Muhammad
- Zubair_MuhammadHI
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)
- aar0n
i see the dates increment daywise. the main issue though, is that the values are not calculating correctly..
Zubair_Muhammad already fixed the increment by month issue, the only issue is that the "value" is constant - aar0n
Thank you!!!