Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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

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

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

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!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors