Yes, it's possible, in a number of ways... Before anything else, we have to understand and agree that we have 2 different Date fields, and therefore the attributes (Year, Month, etc...) have to be different. Once we understand that, we can continue...
A few ways to resolve this problem:
1. Create a separate Calendar table for the Billing Dates and have a separate set of attributes - Billing Year, Billing Month, etc...
2. Or, you can simply join both Calendar tables into the data table and keep those attributes in the same table (you still need separate attributes for Order Date and Billing Date).
3. Finally, you can build the solution with a single Calendar, but it would be the most complex... You'd need to "normalize" your Orders table and have a separate tables for dates - one row for each date. Some thing like this:
Orders (OrderID, ... other Order Attributes)
OrderDates(OrderID, Date, DateTypeFlag), where DateTypeFlag differentiates between Order Date vs. Billing Date.
Then, in all your expressions and dimensions you'd have to use DateTypeFlag in order to specify what date needs to be used.
Even though the later solution only requires a single Calendar, it's the most complex and time consuming. I'd recommend one of the first two options, unless you have any other requirements that definitely call for a single Calendar...
Yes, it's possible, in a number of ways... Before anything else, we have to understand and agree that we have 2 different Date fields, and therefore the attributes (Year, Month, etc...) have to be different. Once we understand that, we can continue...
A few ways to resolve this problem:
1. Create a separate Calendar table for the Billing Dates and have a separate set of attributes - Billing Year, Billing Month, etc...
2. Or, you can simply join both Calendar tables into the data table and keep those attributes in the same table (you still need separate attributes for Order Date and Billing Date).
3. Finally, you can build the solution with a single Calendar, but it would be the most complex... You'd need to "normalize" your Orders table and have a separate tables for dates - one row for each date. Some thing like this:
Orders (OrderID, ... other Order Attributes)
OrderDates(OrderID, Date, DateTypeFlag), where DateTypeFlag differentiates between Order Date vs. Billing Date.
Then, in all your expressions and dimensions you'd have to use DateTypeFlag in order to specify what date needs to be used.
Even though the later solution only requires a single Calendar, it's the most complex and time consuming. I'd recommend one of the first two options, unless you have any other requirements that definitely call for a single Calendar...
With methods 1 and 2, you would indeed have "quarter ordered" and "quarter billed". But that is, after all, "necessary" if you want to be able to select different values for each.
Method 3 won't allow your users (at least without even more complexity) to select orders that were ordered in quarter 1 and billed in quarter 2, for instance. But if that's not a problem, then I'm a big fan of just having a "Quarter" field, a single chart that lets you easily switch between types of dates, and so on. I've done it in several applications, and like the results.
But for most applications, I suspect it is better to just create more than one calendar and have a bunch of different date names.
