Forum Discussion
Calculate the efficiency with accumulated data
Hello Experts, I am new to PowerBi, currently making one report to calculate the efficiency (day used) for hiring.
I have tried different Measures but still cannot get the accummulated and average.
This is my raw data :
| Request Approve Date | Hiring Date | Efficiency (Hiring date minus Request aprove date) | No of People |
| 6-Jan-22 | 7-Apr-22 | 91 | 1 |
| 4-Jan-22 | 25-Apr-22 | 111 | 1 |
| 24-Jan-22 | 10-May-22 | 106 | 1 |
| 18-Feb-22 | 10-Jun-22 | 112 | 1 |
| 19-Apr-22 | 16-Jun-22 | 58 | 1 |
| 21-Feb-22 | 20-Jun-22 | 119 | 1 |
| 10-May-22 | 23-Jun-22 | 44 | 1 |
| 15-Apr-22 | 4-Jul-22 | 79.24931 | 1 |
| 31-Mar-22 | 8-Jul-22 | 98.26389 | 1 |
| 07-Apr-22 | 19-Jul-22 | 102.6611 | 1 |
| 22-12-21 | 21-Jul-22 | 211 | 1 |
| 02-Mar-22 | 21-Jul-22 | 140.4313 | 1 |
| 31-Mar-22 | 15-Aug-22 | 136.2639 | 1 |
| 21-Feb-22 | 15-Aug-22 | 174.3528 | 1 |
| 28-12-21 | 16-Aug-22 | 231 | 1 |
| 24-Jan-22 | 22-Sep-22 | 241 | 1 |
| 25-Aug-22 | 28-09-22 | 34 | 1 |
| 05-Mar | 03-10-22 | 577 | 1 |
| 02-Aug-22 | 11-10-22 | 70 | 1 |
| 30-Jun-22 | 28-10-22 | 119.4667 | 1 |
| 05-Jan-22 | 01-11-22 | 300 | 1 |
I will need the data to calculate by monthly basis, the fomula in excel is :
Efficiency = Hiring date - Request aprove date
Monthly time of hiring = (first month) Total up the efficiency of that month / total people hired that month.
(upcoming months) Total up the efficiency of current month and previous months / total people hired current month and previous months.
Example:
April = (91+111)/(1+1)=101 days
May = (91+111+106)/(1+1+1)=103 days
My image of the result is as below:
| Month | Efficiency |
| Apr | 101 |
| May | 103 |
| Jun | 92 |
| Jul | 97 |
| Aug | 113 |
| Sep | 117 |
| Oct | 141 |
| Nov | 150 |
Thank you for attending my post.
you can create a date table and create a measure
Measure = VAR _eff=sumx(FILTER(all('Table'),'Table'[Hiring Date]<=max('Table'[Hiring Date])),'Table'[Efficiency]) var _num=CALCULATE(COUNTROWS('Table'),FILTER(all('Table'),'Table'[Hiring Date]<=max('Table'[Hiring Date]))) return _eff/ _numpls see the attachment below
3 Replies
ryan_mayuSuper User
you can create a date table and create a measure
Measure = VAR _eff=sumx(FILTER(all('Table'),'Table'[Hiring Date]<=max('Table'[Hiring Date])),'Table'[Efficiency]) var _num=CALCULATE(COUNTROWS('Table'),FILTER(all('Table'),'Table'[Hiring Date]<=max('Table'[Hiring Date]))) return _eff/ _numpls see the attachment below
