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 Experts!
I am very new to Power BI , and trying to calculate Rolling 12 month total and average for the department headcounts based on start date of department in another table.
Headcount table is as below
Report Date | Department Name | Headcount |
31/05/2019 | Department Layer 21 | 1 |
30/06/2019 | Department Layer 21 | 659 |
31/07/2019 | Department Layer 21 | 646 |
31/08/2019 | Department Layer 21 | 660 |
31/07/2018 | Department Layer 22 | 3 |
31/08/2018 | Department Layer 22 | 3 |
30/09/2018 | Department Layer 22 | 164 |
31/10/2018 | Department Layer 22 | 180 |
30/11/2018 | Department Layer 22 | 183 |
31/12/2018 | Department Layer 22 | 180 |
31/01/2019 | Department Layer 22 | 185 |
28/02/2019 | Department Layer 22 | 186 |
31/03/2019 | Department Layer 22 | 185 |
30/04/2019 | Department Layer 22 | 186 |
31/05/2019 | Department Layer 22 | 189 |
31/07/2019 | Department Layer 22 | 286 |
31/08/2019 | Department Layer 22 | 289 |
31/07/2018 | Deprtment Layer 1 | 1205 |
31/08/2018 | Deprtment Layer 1 | 1308 |
30/09/2018 | Deprtment Layer 1 | 1289 |
31/10/2018 | Deprtment Layer 1 | 1163 |
30/11/2018 | Deprtment Layer 1 | 1288 |
31/12/2018 | Deprtment Layer 1 | 1274 |
31/01/2019 | Deprtment Layer 1 | 1277 |
28/02/2019 | Deprtment Layer 1 | 1300 |
31/03/2019 | Deprtment Layer 1 | 1315 |
30/04/2019 | Deprtment Layer 1 | 1338 |
31/05/2019 | Deprtment Layer 1 | 1321 |
30/06/2019 | Deprtment Layer 1 | 1170 |
31/07/2019 | Deprtment Layer 1 | 1288 |
31/08/2019 | Deprtment Layer 1 | 1312 |
The dates when the department starts is in below table
Department Name | Department StartDate |
Deprtment Layer 1 | 31/07/2017 |
Department Layer 22 | 31/03/2019 |
Deprtment Layer 21 | 30/06/2019 |
The measure i want to create is as below
Report Date | Department Name | Measure |
- | Deprtment Layer 1 | Rolling 12 months data from the latest report date backwards |
- | Department Layer 22 | Since departments start is in 31/3/2019, rolling average of department from latest report date backwards till 31/3/2019 |
- | Department Layer 21 | Since departments start is in 30/06/2019, rolling average of department from latest report date backwards till 30/06/2019 |
Thanks in advance
DJ
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
@Anonymous ,
Could you please explain more details about "latest report date backwards"?
Regards,
Jimmy Tao
Hi Jimmy,
Thanks for the response!
Details:
eg: Headcount table has 12 months of data for a particular department, but the Departmnet table suggests that the start date of the department is just 3 months back, so when doing the rolling average only consider the last three months of data.
In short consider the start date of the department from the department table while doing rolling average.
Thanks
Divya Joy
Hi,
You may download my PBI file from here.
Hope this helps.
Ashish,
I could open the file after upgrading my Power Bi desktop.
Thanks for the quick turnaround, the solution is perfect 🙂
Regards
DJ
You are welcome.
H Ashish,
My version of PBI is old, and i am not able to open this file. Any thoughts?
Thanks
DJ
No.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |