I need to develop a dashboard where to compare the following type of data:
- Sales plan (info provided by month)
- Sales actuals (info provided by quarter)
- Sales goal (info provided by quarter).
Also, this information is provided by business category and region and includes the revenue and units.
What I did was to create two tables, the first one to store the information by month and the other one by quarter. Now I can't find a way to relate them since creating keys based in business category, month (in the quarter table, I created a calculated field that assign a month date depending on the Q), region and type of data produces a key that won't find a match in the other table (because of the type).
Example of table 1
Business Period Type Region Revenue Units Jeans 01/11/2012 Plan 1 South 15000 22500 Tops 01/11/2012 Plan 1 North 40500 60750 Shoes 01/11/2012 Plan 1 East 30000 45000 Jeans 01/11/2012 Plan 2 South 12000 18000 Tops 01/11/2012 Plan 2 North 38000 57000 Shoes 01/11/2012 Plan 2 East 27000 40500
Example of table 2:
Business Period Type Region Revenue Units Date (Calculated based on Q) Jeans Q113 Goal South 20000 30000 01/11/2012 Tops Q113 Goal North 45000 67500 01/11/2012 Shoes Q113 Goal East 35000 52500 01/11/2012 Jeans Q412 Actual South 19000 28500 01/08/2012 Tops Q412 Actual North 43000 64500 01/08/2012 Shoes Q412 Actual East 29000 43500 01/08/2012
I will need to make comparisons of sales plans vs goals and actuals of their respective Period of time. What do you suggest?
Thank you for your time
If what you want to do is create a basic relationship so you can report on a quarterly basis then all you need is an external date table that has a column with unique, contiguous dates for the period in question along with columns for quarters (and anything else required). You can then relate the date column in each of your fact tables to your date table and if you drag in the Quarter column to your pivot then you will see the desired result.
In some circumstances it also makes sense to break down a quarterly number to report on a monthly basis - normally it would make more sense for the actual to be at the lower granularity with the comp at a quarterly level but maybe that's what you are trying to do. If so a measure such as this will help:
=IF(HASONEVALUE(DimDate[Month]), CALCULATE(SUM(FactB[Revenue]), ALL(DimDate[Date],DimDate[Month]), FactB[Type]="Goal")/3, CALCULATE(SUM(FactB[Revenue]),FactB[Type]="Goal") )
Hope this makes sense, I saved my 2 minute test model here for reference.