MTD, QTD, YTD Calculations

[Mackrel]

What is the best way to develop the following measures within BO:MTD Sales, QTD Sales, YTD Sales.

My data model is dimensional and very straight forward. Fact table data is stored at the daily level - the time dimension contains the following fields(day, week, month, quarter, year).

I don't have the option of storing these values on the fact table.

Any help would be greatly appreciated.

 

[skrandel]

In the ideal world, you'd have a table in your database that holds the date ranges you care about. For example, have a table that has 3 columns: range_name, start_date, end_date.

YTD 1/1/2002 12/31/2002
QTD 10/1/2002 12/31/2002
MTD 11/1/2002 11/30/2002

Something like that.

Or just have 3 simple tables with 1 row in them. You can then use those 3 tables in 3 separate contexts and do a join where your fact_date is between the start and end dates. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com