cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Re: How to create a Calculated Table for Future Values in from a Unique Name list

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. 

 

TypeNameDateValue 
1a2/28/2018Last known Value (for "Name" a) * 0.77^(1/12)
1a3/31/2018Last known Value (for "Name" a) * 0.77^(1/12)
1a4/30/2018Last known Value (for "Name" a) * 0.77^(1/12)
1a5/31/2018Last known Value (for "Name" a) * 0.77^(1/12)
1a6/30/2018Last known Value (for "Name" a) * 0.77^(1/12)
1a7/31/2018Last known Value (for "Name" a) * 0.77^(1/12)
Last known Value (for "Name" a) * 0.77^(1/12)
2b2/1/2018Last known Value (for "Name" b) * 0.77^(1/12)
2b3/31/2018Last known Value (for "Name" b) * 0.77^(1/12)
Last known Value (for "Name" b) * 0.77^(1/12)
2b1/31/2020Last known Value (for "Name" b) * 0.77^(1/12)

 

 

what i need is 

TypeNameDateValue 
1a2/28/2018Last known Value (for "Name" a) * 0.77^(1/12)
1a3/31/2018Value predicted above * 0.77^(1/12)
1a4/30/2018Value predicted above * 0.77^(1/12)
1a5/31/2018Value predicted above * 0.77^(1/12)
1a6/30/2018Value predicted above * 0.77^(1/12)
1a7/31/2018Value predicted above * 0.77^(1/12)
2b2/1/2018Last known Value (for "Name" b) * 0.77^(1/12)
2b3/31/2018Value predicted above * 0.77^(1/12)
2b1/31/2020Value predicted above * 0.77^(1/12)
Highlighted
Super User IV
Super User IV

Re: How to create a Calculated Table for Future Values in from a Unique Name list

Hi @aar0n,

 

I am not sure whom you are replying to.  Do you need any help from me?  Did my solution work?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Advocate II
Advocate II

Re: How to create a Calculated Table for Future Values in from a Unique Name list

hi 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

Highlighted
Super User IV
Super User IV

Re: How to create a Calculated Table for Future Values in from a Unique Name list

Hi,

 

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Advocate II
Advocate II

Re: How to create a Calculated Table for Future Values in from a Unique Name list

@Ashish_Mathur 

 

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

Highlighted
Super User III
Super User III

Re: How to create a Calculated Table for Future Values in from a Unique Name list

@aar0n

 

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]
Try my new Power BI game Cross the River
Highlighted
Advocate II
Advocate II

Re: How to create a Calculated Table for Future Values in from a Unique Name list

@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.. 

 

 

TypeDateLast known Value * (0.77^(1/12))^Value that represents monthLast known Value * (0.77^(1/12))
Last known value1/31/201729.6929.69
calculated2/28/201828.424312729.05033142
calculated3/31/201827.8119132528.42444444
calculated4/30/201827.2127078927.81204215
calculated5/31/201826.6264123627.21283401
calculated6/30/201826.0527485226.62653577
calculated7/31/201825.4914442226.05286927
calculated8/31/201824.9422331725.49156236
calculated9/30/201824.4048548324.94234877
calculated10/31/201823.8790542624.40496794
calculated11/30/201823.3645820223.87916493
calculated12/31/201822.8611940523.36469031
calculated1/31/201922.3686515222.8613
calculated2/28/201921.8867207822.36875519
Highlighted
Super User III
Super User III

Re: How to create a Calculated Table for Future Values in from a Unique Name list

@aar0n

 

I will look into it

Try my new Power BI game Cross the River
Highlighted
Super User III
Super User III

Re: How to create a Calculated Table for Future Values in from a Unique Name list

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)
 
Try my new Power BI game Cross the River

View solution in original post

Highlighted
Advocate II
Advocate II

Re: How to create a Calculated Table for Future Values in from a Unique Name list

Thank you!!!

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors