Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
TeigeGao
Solution Sage
Solution Sage

Hi @Anonymous ,

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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.

 

TeigeGao
Solution Sage
Solution Sage

Hi @Anonymous ,

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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!
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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