compare counts by 2 different dates

[#1]

Hi I'm am trying to calculate the closure rate for cases by month.

An example of the data is as follows:

Case ID	Created Date	Closed Date
123	1/7/2014	12/7/2014
234	5/7/2014	6/8/2014
345	13/8/2014	15/8/2014
456	15/8/2014	28/8/2014

The results I need to see are:

Year	Month	No. Cases Created	No. Cases Closed	Closure Rate
2014	Jul	2	1	50%
2014	Aug	2	3	150%

My biggest issue is the different dates. I can create a master calendar, but how can I link two different date fields from the same table to it, as they can't have the same field name?

Any help would be appreciated.

[#2]

For this case you must have two different calendars one for [Created Date] and other for [Closed Date] . You can have multiple calendars if you have multiple dates and you will need to apply selections or create comparative (rolling periods) analysis. Just name the second one with a different names not only for the table itself but also for the fields, thus:

CalCreateDate

MonthCreateD 

MonthSerialCreateD

YearCreateD

CalClosedDate

MonthClosedD

MonthSerialClosedD

YearClosedD

and so on.

[jonathandienst · Accepted solution]

Have a look here:

Canonical Date

[#4]

Thanks. fixed it completely!