cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thomas-B-Hudson
Frequent Visitor

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

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:

Staff45Total
Annu.Bhutta03737
Eilysh.Websdale351045
Hannah.Lelliott402565
Javeria.Khan02929
Jovette.Daniel122739
Koralee.Swete82432
Lauren.Haddow5843101
Marta.Noskova369
Nicole.Henderson141630
Olivia.Simunec266894
Ori.Newby222648
Rafia.Khan000
Sabrina.Benchaib323365
Sehr.Aslam000
Sheridan.Gerrard5747104
Stephanie.Gainsford  1982101
Valentina.Loch191231
Verena.Braun17482156
Grand Total423572986

 

Calendar Table:

Week NumberQuarter NumberYearWeek QTR Count
1120221
2120222
3120223
4120224
5120225
6120226
7120227
8120228
9120229
101202210
111202211
121202212
131202213
14220221
15220222
16220223
17220224
18220225
19220226
20220227
21220228
22220229
232202210
242202211
252202212
262202213
27320221
28320222
29320223
30320224
31320225
32320226
33320227
34320228
35320229
363202210
373202211
383202212
393202213
40420221
41420222
42420223
43420224
44420225
45420226
46420227
47420228
48420229
494202210
504202211
514202212
524202213

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.

Staff45TotalWeeks Into QuarterAvg PW
Annu.Bhutta0373784.625
Eilysh.Websdale35104585.625
Hannah.Lelliott40256588.125
Javeria.Khan0292983.625
Jovette.Daniel12273984.875
Koralee.Swete8243284
Lauren.Haddow5843101812.625
Marta.Noskova36981.125
Nicole.Henderson14163083.75
Olivia.Simunec266894811.75
Ori.Newby22264886
Rafia.Khan00080
Sabrina.Benchaib32336588.125
Sehr.Aslam00080
Sheridan.Gerrard5747104813
Stephanie.Gainsford            1982101812.625
Valentina.Loch19123183.875
Verena.Braun17482156819.5
Grand Total4235729868123.25

 

Does anyone know how I can calculate this?

Thanks,
Tom

1 ACCEPTED SOLUTION
Thomas-B-Hudson
Frequent Visitor

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.

View solution in original post

6 REPLIES 6
Thomas-B-Hudson
Frequent Visitor

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Community Champion
Community Champion

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... 




Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors