cancel
Showing results for
Did you mean:
Helper I

## 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:

 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

1 ACCEPTED SOLUTION
Helper I

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.
6 REPLIES 6
Helper I

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

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Super User

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
Helper I

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.

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] ) )``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Connect to me on LinkedIn || Need consulting? Hire me for a Power BI gig on UpWork.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors