cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mrangelWG Frequent Visitor
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 - MonthStatement Date - YearVendor NameGross AmountData Group Type Job CategorySecond CategoryNameWeek Ending Date
12018Supplier A$1,055.95Group AJob AStaffTime and MaterialsAccting12/30/2017
12018Supplier A$829.68Group AJob AStaffTime and MaterialsAccting1/6/2018
12018Supplier A$1,546.25Group AJob AStaffTime and MaterialsAccting1/13/2018
12018Supplier A$1,146.46Group AJob AStaffTime and MaterialsAccting1/20/2018
12018Supplier B$2,000.00Group B Job BProjectTime and MaterialsProject12/30/2017
12018Supplier B$4,000.00Group B Job BProjectTime and MaterialsProject1/6/2018
12018Supplier B$2,000.00Group B Job BProjectTime and MaterialsProject1/13/2018
12018Supplier C$548.24Group CJob cStaffTime and MaterialsClerical12/30/2017
12018Supplier C$708.17Group CJob DStaffTime and MaterialsEngineering12/30/2017
12018Supplier C$554.22Group CJob DStaffTime and MaterialsEngineering1/6/2018
12018Supplier C$1,231.60Group CJob DStaffTime and MaterialsEngineering1/13/2018
12018Supplier D$1,820.00Group D Job EProjectTime and MaterialsProject12/30/2017
12018Supplier D$1,820.00Group D Job EProjectTime and MaterialsProject12/30/2017
12018Supplier D$1,820.00Group D Job EProjectTime and MaterialsProject12/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. 

 

Can you please help? 

 

Thanks!

Mel

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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 =
ADDCOLUMNS (
    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:

PBIDesktop_nYV2WsT7NC.png

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:

PBIDesktop_gVxsXwZ9LJ.png

Best Regards,

Teige

4 REPLIES 4
mrangelWG Frequent Visitor
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.
 
Any suggestions, please?

Thanks!
 
Community Support Team
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 =
ADDCOLUMNS (
    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:

PBIDesktop_nYV2WsT7NC.png

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:

PBIDesktop_gVxsXwZ9LJ.png

Best Regards,

Teige

mrangelWG Frequent Visitor
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

mrangelWG Frequent Visitor
Frequent Visitor

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

hi @TeigeGao 

 

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. 

 

Please help.

 

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 416 members 4,226 guests
Please welcome our newest community members: