cancel
Showing results for
Did you mean:
Highlighted

## 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

 Type Name Last date in dataset Last Known Value 1 a 1/31/2017 10 2 b 1/31/2017 15 3 c 1/31/2017 12 4 d 1/31/2017 20

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

 Type Name Date Value 1 a 2/1/2018 Last known Value (for "Name" a) * 0.77^(1/12) 1 a 2/2/2018 Value predicted above * 0.77^(1/12) 1 a 2/3/2018 Value predicted above * 0.77^(1/12) 1 a 2/4/2018 Value predicted above * 0.77^(1/12) 1 a 2/5/2018 Value predicted above * 0.77^(1/12) 1 a 2/6/2018 Value predicted above * 0.77^(1/12) … … … … 1 a 1/1/2020 Value predicted above * 0.77^(1/12) 2 b 2/1/2018 Last known Value (for "Name" b) * 0.77^(1/12) 2 b 2/2/2018 Value predicted above * 0.77^(1/12) … … … … 2 b 1/1/2020 Value 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

## 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 =
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
Highlighted
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
Highlighted
Super User III

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

HI
&nbsp;
From your last post I noticed this. So I think adjusting the POWER by -1 should fix it.
&nbsp;
My VALUE =
'Table'[Last Known Value ]
* ( 0.77
^ ( 1 / 12 ) )
^ ('Table'[Value that represents the month]-1)
&nbsp;
Try my new Power BI game Cross the River
19 REPLIES 19
Highlighted
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 =
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
Highlighted

## 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

## 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

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

## 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

## 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

## 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
Highlighted

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

 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)
Highlighted
Super User IV

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

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### 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

Learn about the exciting things that happened in July.

#### 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

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

Top Solution Authors
Top Kudoed Authors