Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Can you please help?
Thanks!
Mel
Solved! Go to Solution.
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:
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
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.
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:
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
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
I forgot to add, that i have created the following measures but they did not work:
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |