cancel
Showing results 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

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors