Weekly Average

[M_SBS_6]

Hi, 

I have a table which has staff name and the another column which counts the number of bookings. 

staff name.    Bookings

kev.                    900

sara.                   750

darren.                600

The bookings has a measure: distinctcount(booking[id]). 

Within this table I have added a filter to show createdon is in this year. This gives me what I initially need. 

However, I'd like another column which calculates the average bookings made per week. If I take the top example of 900 bookings and divide it by 43 (as that's the current week we are in calendar year) then Kev on average books 21 meetings per week. 

Can someone please advise how I do this? 

[Praful_Potphode · Accepted solution]

Hi @M_SBS_6 

You can create below measure to store current week number .

Current Week number = WEEKNUM(TODAY())

Then divide your distinct measure by this number to arrive at avg booking per week as shown below.

Avg Booking per Week = CONVERT(
    DIVIDE([Distinct Bookings], //here you can put your measure name
                        [Current Week number]
)
,INTEGER
)

Try above solution and let me know.

Please give Kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[v-priyankata]

Hi @M_SBS_6 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@Ritaf1983 @Praful_Potphode Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @M_SBS_6 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[Ritaf1983 · Accepted solution]

Hi @M_SBS_6 

You’ll need a Calendar table with a column for the week number (WeekNum or WeekOfYear) based on your CreatedOn date.
Once you have that, you can create a measure like this:

Avg Bookings per Week =
DIVIDE(
[Bookings],
MAX('Calendar'[WeekOfYear])
)

This will divide the total number of bookings by the current week number (e.g., 43) to calculate the average bookings per week up to the current point in the year.
If you want the calculation to adjust automatically by year, make sure your Calendar table is filtered to the selected year.

If I didn’t fully capture your scenario, please share a sample file (without sensitive data) through a public link so it can be reviewed in context.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Praful_Potphode · Accepted solution]

Hi @M_SBS_6 

You can create below measure to store current week number .

Current Week number = WEEKNUM(TODAY())

Then divide your distinct measure by this number to arrive at avg booking per week as shown below.

Avg Booking per Week = CONVERT(
    DIVIDE([Distinct Bookings], //here you can put your measure name
                        [Current Week number]
)
,INTEGER
)

Try above solution and let me know.

Please give Kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful