Calculating M/M, Q/Q, Y/Y Sales Data from a database with weekly totals

[katejohnson]

My database is organized by [Week Ending] with many fields of data with performance measures.



I have created a monthly dashboard report that i want to show the Monthly totals for all of the performance measures and I want to calculate the M/M, Q/Q, and Y/Y sales.

I have grouped the [Week Ending] by month on my report and used a SUM or AVG functions for all of the performance measures to get the monthly totals. This worked fine, but I can't figure out how to calculate the M/M, etc.

Thanks!

[rpeare]

Create a series of labels in your query

MonthLabel: datepart("yyyy", [week ending]) & right(datepart("m",[week ending]))
QuarterLabel: datepart("yyyy", [week ending]) & iif(cint(datepart("m",[week ending])) between 1 and 3, "-Q1",iif(cint(datepart("m",[week ending])) between 4 and 6, "-Q2",iif(cint(datepart("m",[week ending])) between 7 and 9, "-Q3",iif(cint(datepart("m",[week ending])) between 10 and 12, "-Q4",null))))
YearLabel: datepart("yyyy", [week ending])

Then on your report you can summarize by any one of these labels

NOTE: I did this without actually running a test so you may have to play with the parentheses to get it right.