# How do I create date dimension(s) so the output is ytd, mtd, dtd?

on
Newby here,

I need, mostly step by step, in configuration a date dimension that will provide year to date, month to date or day to date.

I would think this is a common occurrence but I have not been successful in finding this.

What you say? 500 points 4 u.
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
What do you mean by date dimension?
you mean a where clause?
it can be done in multiple ways, using datepart or datediff, or BETWEEN...

Here's some examples:
1- Year to date
WHERE DATEPART(yyyy,datefield)=DATEPART(yyyy,getdate())
2- Month to date
WHERE DATEDIFF(mm,0,datefield)=DATEDIFF(mm,0,getdate())
3- Day to date
WHERE DATEDIFF(dd,0,datefield)=DATEDIFF(dd,0,getdate())

Commented:
In building my analysis cube I currently am using a date dimension of which I built 4 hierarchies:

The date dimension has columns whcih reflect Calendar \ Fiscal colums such as fiscal week , calendar year etc........

Hierarchy Example:
FY-FQ-FM-FW-FD

These are working fine as a drill through from FY thru FD.

What I need now is to structure a hierarchy which will give me FYTD functionality. This would be represented as   For an example:
Fiscal Year begins in October and it is now November, I need:
1. year to date accumuitve
October total is 100 November (to date) is 72 YTD would = 172
Commented:
Ok, it's cube dimensions, sorry, I haven't really worked with cubes, can't help you there.

Commented:
@stalhw - Thx, though.  Tell one of your buddies that may know I will split points.

Do more with