Dynamic use of calculated value in power query

[SvCappellen]

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?

https://we.tl/t-125ZSaNwjZ

Kind regards,
Stephanie

[v-yingjl]

Hi @SvCappellen ,

Based on your description, seems not certain what is your expected output.

Some questons in this case that want to know:

1. "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?
2. 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/38490

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.

[SvCappellen]

 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.

1. 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 · Accepted solution]

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.