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

How 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

TypeNameLast date in datasetLast Known Value 
1a1/31/201710
2b1/31/201715
3c1/31/201712
4d1/31/201720

 

 

What i need, is to apply a formula for each "Name". The formula will take the last known "Value" for each unique "Name", and multiplies by 0.77^(1/12) for all future dates until 2020.

 

an example of what i'm looking for 

TypeNameDateValue 
1a2/1/2018Last known Value (for "Name" a) * 0.77^(1/12)
1a2/2/2018Value predicted above * 0.77^(1/12)
1a2/3/2018Value predicted above * 0.77^(1/12)
1a2/4/2018Value predicted above * 0.77^(1/12)
1a2/5/2018Value predicted above * 0.77^(1/12)
1a2/6/2018Value predicted above * 0.77^(1/12)
1a1/1/2020Value predicted above * 0.77^(1/12)
2b2/1/2018Last known Value (for "Name" b) * 0.77^(1/12)
2b2/2/2018Value predicted above * 0.77^(1/12)
2b1/1/2020Value predicted above * 0.77^(1/12)

 

The biggest issue for me isnt the formula.. ive just been struggling how to figure out how to build a date list for each unique name that starts after the original query ends.

 

 

Thanks a lot for the help,

Aaron

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

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

View solution in original post

Highlighted
Super User III
Super User III

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

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] )

 

Try my new Power BI game Cross the River

View solution in original post

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

19 REPLIES 19
Highlighted
Super User III
Super User III

Re: How 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 )
)
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

That worked amazing! thank you so much!

 

would you be able to explain how youre using the Addcolumns, generate, and Generateseries together?

 

for future reference (i'm definitely going to be using this again) i am also wondering how you would do the same process, except the dates increase  by 1 month instead of 1 day

Highlighted
Super User III
Super User III

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

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

 

 

 

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

@aar0n

 

There is a 3rd argument of GenerateSeries,,,,(Interval) which allows you to give gap between dates

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

I 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

Highlighted
Super User III
Super User III

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

Hi @aar0n

 

I will look into it and get back to you

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 @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] )

 

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

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,

 

You may download my solution from here.


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

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!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

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

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors