cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Average weekly spend for each month by number of week

Hi,

I am trying to create a rolling 12 month matrix showing the average weekly spend for each month.  The issue I am running into is that the monthly totals would need to be averaged by the actual number of Thursdays in each month since invoicing closes each Thursday.  Some of the weeks are partial and I am not sure how to account for these partial weeks in my calculations.

I have the following data to work with:

 Statement Date - Month Statement Date - Year Vendor Name Gross Amount Data Group Type Job Category Second Category Name Week Ending Date 1 2018 Supplier A \$1,055.95 Group A Job A Staff Time and Materials Accting 12/30/2017 1 2018 Supplier A \$829.68 Group A Job A Staff Time and Materials Accting 1/6/2018 1 2018 Supplier A \$1,546.25 Group A Job A Staff Time and Materials Accting 1/13/2018 1 2018 Supplier A \$1,146.46 Group A Job A Staff Time and Materials Accting 1/20/2018 1 2018 Supplier B \$2,000.00 Group B Job B Project Time and Materials Project 12/30/2017 1 2018 Supplier B \$4,000.00 Group B Job B Project Time and Materials Project 1/6/2018 1 2018 Supplier B \$2,000.00 Group B Job B Project Time and Materials Project 1/13/2018 1 2018 Supplier C \$548.24 Group C Job c Staff Time and Materials Clerical 12/30/2017 1 2018 Supplier C \$708.17 Group C Job D Staff Time and Materials Engineering 12/30/2017 1 2018 Supplier C \$554.22 Group C Job D Staff Time and Materials Engineering 1/6/2018 1 2018 Supplier C \$1,231.60 Group C Job D Staff Time and Materials Engineering 1/13/2018 1 2018 Supplier D \$1,820.00 Group D Job E Project Time and Materials Project 12/30/2017 1 2018 Supplier D \$1,820.00 Group D Job E Project Time and Materials Project 12/30/2017 1 2018 Supplier D \$1,820.00 Group D Job E Project Time and Materials Project 12/30/2017

I need an average weekly spend for each month that would capture the actual number of weeks by count of Thursday for each month.

i.e divide monthly revenue for June 2018 by 4 weeks, for July 2018 by 4 weeks, aug 2018 by 5 weeks, etc.

Thanks!

Mel

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Average weekly spend for each month by number of week

Hi @mrangelWG ,

In your sccenario, we can create a calendar table like below:

```calend =
CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 12, 31 ) ),
"weekday", WEEKDAY ( [Date], 2 ),
"month", MONTH ( [Date] )
)```

Then create a table with the count of Thursday for each month like below:

```Table =
SUMMARIZE (
calend,
calend[month],
"Th", COUNTROWS ( FILTER ( calend, calend[weekday] = 4 ) )
)```

We will get a table:

Then we can create a calculated column in your data table to get the month of each date, after that we can create a measure like below:

`Measure = DIVIDE(SUM(Table1[Gross Amount]),MIN('Table'[Th]))`

The result will like below:

Best Regards,

Teige

4 REPLIES 4
Frequent Visitor

## Re: Average weekly spend for each month by number of week

I forgot to add, that i have created the following measures but they did not work:

average = SUM(Spend Data'[Gross Amount])/DISTINCTCOUNT('date'[Weekday])/[final total week]
days_per week = CALCULATE(SUM('date'[weekday]),FILTER(ALLSELECTED('date'),'date'[weeknum]=MAX('date'[weeknum])))
total week = COUNTX(FILTER(VALUES('date'[weeknum]),[days_per week]=28),'date'[weeknum])
final total week =
IF (
[year or year-month] = 1
&& SELECTEDVALUE ( 'date'[year] ) = YEAR ( TODAY () ),
CALCULATE ( [total week], FILTER ( 'Molina Spend Data', 'Molina Spend Data'[Week Ending Date] <= [maxdate] ) ),
[total week]
)
maxdate = CALCULATE(MAX('Molina Spend Data'[Week Ending Date]),ALLSELECTED('date'))
average = SUM('Spend Data'[Gross Amount])/DISTINCTCOUNT('date'[Weekday])/[final total week]

The actual calculation would technically be the following:
Avg Spend/Week = DIVIDE([Cumulative Total],4,BLANK())
but it does not account for months that have 5 weeks.

Thanks!

Community Support Team

## Re: Average weekly spend for each month by number of week

Hi @mrangelWG ,

In your sccenario, we can create a calendar table like below:

```calend =
CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 12, 31 ) ),
"weekday", WEEKDAY ( [Date], 2 ),
"month", MONTH ( [Date] )
)```

Then create a table with the count of Thursday for each month like below:

```Table =
SUMMARIZE (
calend,
calend[month],
"Th", COUNTROWS ( FILTER ( calend, calend[weekday] = 4 ) )
)```

We will get a table:

Then we can create a calculated column in your data table to get the month of each date, after that we can create a measure like below:

`Measure = DIVIDE(SUM(Table1[Gross Amount]),MIN('Table'[Th]))`

The result will like below:

Best Regards,

Teige

Frequent Visitor

## Re: Average weekly spend for each month by number of week

Thank you @TeigeGao

This worked.

How I can maintain this as a rolling 12 month calendar to date with the tables?

Do I need to add an additional table for 2019 and so forth or add additional columns for the count of Thursdays for each year?

Thanks!

Mel

Frequent Visitor

## Re: Average weekly spend for each month by number of week

I have built everything according to the instructions provided but for some odd reason it either calculates by 4 weeks when I use MIN or 5 weeks when I use MAX.  I need for the my visual to show the average weekly spend for each month and it must be calculated by each month's corresponding number of Thursdays.

Announcements

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 416 members 4,226 guests
Recent signins: