Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 @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 ) )
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
There is a 3rd argument of GenerateSeries,,,,(Interval) which allows you to give gap between dates
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
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] )
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) |
Hi @aar0n,
I am not sure whom you are replying to. Do you need any help from me? Did my solution work?
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?
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
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]
@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 |
I will look into it
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.
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) |
Hi,
You may download my solution from here.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |