Forum Discussion
Dynamic use of calculated value in power query
Hello everyone!
We have two lists with vacancies: 1. Open 2. Filled
For both files we calculate the processing times. For the file with open vacancies we calculate the processing time from posting date until today and for the filled vacancies from posting date till date filled in.
In de open vacancies we calculate with the average processing time of last year the expected startdate. Now we put this in a separate Excel file, but we want to make this dynamic.
We want to use per posting date the average processing time of the two months before that date.
Can anyone help us with that?
Kind regards,
Stephanie
Hi SvCappellen ,
You can try this query based on your previous sample file:
let Bron = Table.Combine({Filled, Open}), #"Removed Columns" = Table.RemoveColumns(Bron,{"Processingtime 2020", "Expected startdate"}), #"Added Custom" = Table.AddColumn( #"Removed Columns", "Average", each if [Date filled] = null then let x = _ in List.Average( Table.SelectRows( #"Removed Columns",each ([FunctionGroup] = x[FunctionGroup] and [Posting date] <= x[Posting date] and [Posting date] >= Date.AddMonths(x[Posting date],-2) ) )[Processing time] ) else null,type number ), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Expected Start time", each Date.From(Number.From([Posting date]) + [Average]),type date) in #"Added Custom1"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
v-yingjlCommunity Support
Hi SvCappellen ,
Based on your description, seems not certain what is your expected output.
Some questons in this case that want to know:
- "We want to use per posting date the average processing time of the two months before that date." What did 'two months before that date' mean?
- In your sample file, you combined the 'Open' table and the 'Filled' table as a single table. Did you want to create a column to calculate some 'average' value in this table or anything else?
Could you please consider taking an example from the sample file to explain the expected output in details so that members in the community could help you better.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks for the questions.
1. We want to know for all dates what the average processing time is of the last two months. So for example for a posting date of July 30, 2021 we want to calculate the average processing times of all records that fall in the same function group with a posting date between April 30 and July 29.- We want to create an extra column in the combined table or if it’s easier a separate (date)table with the average per date. That is our idea now, but maybe there is a better solution for this problem.
So as an example:
For the posting date Wednesday May 26, 2021 we want to calculate the average processing time from the filled and open vacancies between March 26 and May 25 to calculate the expected date filled for the open vacancy.
Now we use to calculate the expected date filled the average processing time of last year from a separate Excel file that we manually fill. We want to get rid of that extra Excel file.
A example from the sample data:Function FunctionGroup Posting date Date filled Processing time Average Expected startdate A B 12-11-2020 2-12-2020 20 B A 25-11-2020 18-12-2020 23 B A 25-11-2020 13-12-2020 18 D B 25-11-2020 15-12-2020 20 D B 1-12-2020 24-12-2020 23 C B 1-12-2020 19-12-2020 18 C B 1-12-2020 21-12-2020 20 B A 2-12-2020 25-12-2020 23 C B 4-12-2020 22-12-2020 18 A A 4-12-2020 24-12-2020 20 B A 21-12-2020 13-1-2021 23 D B 22-12-2020 9-1-2021 18 A A 24-12-2020 13-1-2021 20 A A 28-12-2020 20-1-2021 23 A A 31-12-2020 18-1-2021 18 D B 22-1-2021 186 = AVERAGE PROCESSING TIME of the last two months before this post date = Post date + average D B 28-1-2021 180 = AVERAGE PROCESSING TIME of the last two months before this post date = Post date + average B B 29-1-2021 179 66,14285714 5-4-2021 D B 16-2-2021 161 = AVERAGE PROCESSING TIME of the last two months before this post date = Post date + average D B 24-2-2021 153 = AVERAGE PROCESSING TIME of the last two months before this post date = Post date + average A A 26-2-2021 151 20,5 18-3-2021 A A 2-3-2021 147 = AVERAGE PROCESSING TIME of the last two months before this post date = Post date + average C A 3-3-2021 146 = AVERAGE PROCESSING TIME of the last two months before this post date = Post date + average The Orange average is calculated from the orange records, the blue from the blue records.
Hope this make our question more clear.
Kind regards,
Stephanie- v-yingjl
Hi SvCappellen ,
You can try this query based on your previous sample file:
let Bron = Table.Combine({Filled, Open}), #"Removed Columns" = Table.RemoveColumns(Bron,{"Processingtime 2020", "Expected startdate"}), #"Added Custom" = Table.AddColumn( #"Removed Columns", "Average", each if [Date filled] = null then let x = _ in List.Average( Table.SelectRows( #"Removed Columns",each ([FunctionGroup] = x[FunctionGroup] and [Posting date] <= x[Posting date] and [Posting date] >= Date.AddMonths(x[Posting date],-2) ) )[Processing time] ) else null,type number ), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Expected Start time", each Date.From(Number.From([Posting date]) + [Average]),type date) in #"Added Custom1"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- We want to create an extra column in the combined table or if it’s easier a separate (date)table with the average per date. That is our idea now, but maybe there is a better solution for this problem.
