Hello Guys, I'm building a report to show the evolution of some KPIs during a certain Time , so, I was wondering if there is a way to achieve the follow.....
Important, this report is updated Weekly , so image that in the update that was made on August 8 i had the following information in my data table
| Sales Person | Date | Number of calls |
| SalesPerson A | August 1st | 10 |
| SalesPerson A | August 2nd | 8 |
| Salesperson A | August 3rd | 5 |
and lets imagine that, at the moment I run the update, in august 8th he didn't feed up the system with information for day 4, 5 6 and 7 .....
so, based on this, I will calculate his average calls from august 1st to august 8th , than we have (10+8+5)/8 days = 23/8 = 2,875
now , imagine that in the report I updated in August 15th my salesperson has updated the data that was missing, such as
| Sales Person | Date | Number of calls |
| SalesPerson A | August 1st | 10 |
| SalesPerson A | August 2nd | 8 |
| Salesperson A | August 3rd | 12 |
| Salesperson A | August 4th | 10 |
| Salesperson A | August 5th | 10 |
| Salesperson A | Augusto 6th ' | 20 |
and so on until August 15th
imagine now the the average calls of this salesperson is 10....
What I need is to somehow store this two calculated averages 2,875 and 10 to display in a report like
| Reported Date | Average |
| Aug 8th | 2,875 |
| Aug 15 | 10 |
have in mind that is possible to my salesperson to record information for the past days .....
Basically what I was thinking to achieve it is
Using Power query
- Get the calculation of the average calls
- Store it in a table that will be manually created to receive this information
next update the calculation will run and append the new average in the table keeping the old (2,875) and inserting the new one, like one "insert into " in SQL.
any idea how can I achieve this ?
thanks
