Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aar0n
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
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

View solution in original post

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

 


Regards
Zubair

Please try my custom visuals

View solution in original post

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)
 

Regards
Zubair

Please try my custom visuals

View solution in original post

19 REPLIES 19
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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

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

 

 

 


Regards
Zubair

Please try my custom visuals

@aar0n

 

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


Regards
Zubair

Please try my custom visuals

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

Hi @aar0n

 

I will look into it and get back to you


Regards
Zubair

Please try my custom visuals

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

 


Regards
Zubair

Please try my custom visuals

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)

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/

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

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/

@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

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

Regards
Zubair

Please try my custom visuals

@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

@aar0n

 

I will look into it


Regards
Zubair

Please try my custom visuals

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)
 

Regards
Zubair

Please try my custom visuals

Thank you!!!

 

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)

Hi,

 

You may download my solution from here.


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.