Rolling total based on start date of department

[Anonymous]

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

[v-yuta-msft]

@Anonymous ,

Could you please explain more details about "latest report date backwards"?

Regards,

Jimmy Tao

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Ashish,

I could open the file after upgrading my Power Bi desktop.
Thanks for the quick turnaround, the solution is perfect 🙂

Regards

DJ

[Ashish_Mathur]

You are welcome.

[Anonymous]

H Ashish,

My version of PBI is old, and i am not able to open this file. Any thoughts?

Thanks

DJ

[Ashish_Mathur]

No.