Calculate the average based on how many weeks in a quarter have passed.

[Thomas-B-Hudson]

Hi All,

I have a report which has an activity date range.

However I was to create a visual which tells me of the number of positions raised by an indivdual in a quarter what is the weekly average number based on the number of weeks passed in a given quarter.

Below is a list of the table I have which tells me the total number of positions raised by staff for the month of April (4) and May - So far (5).

I have quite a robust date table which tells me the activity date and the week number, month number, quarter number and year for each activity date although it is too big to paste in here.

Raw Data:

Staff	4	5	Total
Annu.Bhutta	0	37	37
Eilysh.Websdale	35	10	45
Hannah.Lelliott	40	25	65
Javeria.Khan	0	29	29
Jovette.Daniel	12	27	39
Koralee.Swete	8	24	32
Lauren.Haddow	58	43	101
Marta.Noskova	3	6	9
Nicole.Henderson	14	16	30
Olivia.Simunec	26	68	94
Ori.Newby	22	26	48
Rafia.Khan	0	0	0
Sabrina.Benchaib	32	33	65
Sehr.Aslam	0	0	0
Sheridan.Gerrard	57	47	104
Stephanie.Gainsford	19	82	101
Valentina.Loch	19	12	31
Verena.Braun1	74	82	156
Grand Total	423	572	986

Calendar Table:

Week Number	Quarter Number	Year	Week QTR Count
1	1	2022	1
2	1	2022	2
3	1	2022	3
4	1	2022	4
5	1	2022	5
6	1	2022	6
7	1	2022	7
8	1	2022	8
9	1	2022	9
10	1	2022	10
11	1	2022	11
12	1	2022	12
13	1	2022	13
14	2	2022	1
15	2	2022	2
16	2	2022	3
17	2	2022	4
18	2	2022	5
19	2	2022	6
20	2	2022	7
21	2	2022	8
22	2	2022	9
23	2	2022	10
24	2	2022	11
25	2	2022	12
26	2	2022	13
27	3	2022	1
28	3	2022	2
29	3	2022	3
30	3	2022	4
31	3	2022	5
32	3	2022	6
33	3	2022	7
34	3	2022	8
35	3	2022	9
36	3	2022	10
37	3	2022	11
38	3	2022	12
39	3	2022	13
40	4	2022	1
41	4	2022	2
42	4	2022	3
43	4	2022	4
44	4	2022	5
45	4	2022	6
46	4	2022	7
47	4	2022	8
48	4	2022	9
49	4	2022	10
50	4	2022	11
51	4	2022	12
52	4	2022	13

I would like to achieve something like the below which tells me how many weeks are we into the current quarter and then divide the total number of positions raised in the current quarter by the number of weeks in this case 8.

Staff	4	5	Total	Weeks Into Quarter	Avg PW
Annu.Bhutta	0	37	37	8	4.625
Eilysh.Websdale	35	10	45	8	5.625
Hannah.Lelliott	40	25	65	8	8.125
Javeria.Khan	0	29	29	8	3.625
Jovette.Daniel	12	27	39	8	4.875
Koralee.Swete	8	24	32	8	4
Lauren.Haddow	58	43	101	8	12.625
Marta.Noskova	3	6	9	8	1.125
Nicole.Henderson	14	16	30	8	3.75
Olivia.Simunec	26	68	94	8	11.75
Ori.Newby	22	26	48	8	6
Rafia.Khan	0	0	0	8	0
Sabrina.Benchaib	32	33	65	8	8.125
Sehr.Aslam	0	0	0	8	0
Sheridan.Gerrard	57	47	104	8	13
Stephanie.Gainsford	19	82	101	8	12.625
Valentina.Loch	19	12	31	8	3.875
Verena.Braun1	74	82	156	8	19.5
Grand Total	423	572	986	8	123.25

Does anyone know how I can calculate this?

Thanks,
Tom

[Thomas-B-Hudson · Accepted solution]

I have managed to do a temporary fix for this but if anyone knows a permanent fix until then here it is.

I have a relation from table 1 to my date table.

I added a slicer for activity date to the slicer which let me select a specific range I wanted to report on 

EG - 04/04/2022 - 25/05/2022, which is 8 financial weeks

I then created 3 measure's.

1 - Min Date Range = 

CALCULATE(LOOKUPVALUE(vdimDate[financial_week_number],vdimDate[activity_Date],MIN(vdimDate[activity_Date])))

From the example this would give me the value of 14 as 4/4/2022 is in finacial week 14.

2 - Max Date Range = CALCULATE(LOOKUPVALUE(vdimDate[financial_week_number],vdimDate[activity_Date],MAX(vdimDate[activity_Date])))

From the example this would give me the value of 21 as 25/5/2022 is in finacial week 14.

3 - Date/Week Range Var = Measures_Dimension[Max Date Range] - Measures_Dimension[Min Date Range] +1

This would give me the value of 8.

Note this will not work when you are comparing over two different years.

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.  Please also ensure that you have a Calendar Table with a Weeknumber column in the Calendar Table.

[Thomas-B-Hudson]

Hi Ashish,

I'm not able to provide a download link for you PBIx because my organisation doesn't allow us to share/export this.

I have updated my topic thread however with the table I am using. My date table does have a week number column associated with it as well as month, quarter and year

[Ashish_Mathur]

Hi,

Is the first table in your Original post, the raw data table?  Why do you not have a Date column?  Also, please share a Calendar table with a week number column (which restarts) at the beginning of each quarter.

[Thomas-B-Hudson]

Hi Ashish,

The first table is my raw data, I have just updated the original post with a calendar table.

Note I don't have a Week Qtr Count column in my actual calendar table yet though but I can create a custom column to add this in.

[danextian]

Hi @Thomas-B-Hudson ,

Based on just the information provided, you can simply create a measure that returns a division an example is

MyMeasure =
DIVIDE ( SUM ( Table[Column1] ), SUM ( Table[Column2] ) )

If this doesn't answer your query, please refer to this link on how the community can help you better: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...