I think that that formulas that you'll want to become familiar with are:
DATETRUNC which clips a date to a datepart - ie DATETRUNC('month',TODAY()) returns 4/1/2014.
DATEADD which came in handy in obtaining the last day of the month (there has got to be a more elegant way to do this!).
ISNULL - returns true if test returns null - used to look for null EndDt and replace with today per one of your conditions.
I'm certain that there are folks here who could do this more elegantly, but I solved it as follows:
Created 2 new Dimensions called StartDt_Calculated and EndDt_Calculated with the following formulas:
IF DATETRUNC('month',[StartDt]) <= today()
IF [EndDt] < DATETRUNC('month',NULL)
ELSEIF DATETRUNC('month',[EndDt]) = DATETRUNC('month',TODAY())
ELSEIF DATETRUNC('month',[EndDt]) > DATETRUNC('month',TODAY())
ELSEIF ISNULL([EndDt])= TRUE
I believe that these two formulas satisfy all of the conditions that you outlined in your post. From there I used a DATEDIFF to compare the two calculated values:
This returns the number of days eligible in the current month.
Attached is an example. Hope this helps!
Date Calculations.twbx 31.3 KB
Thank you SO much for your assistance with this project. It appears that this works perfectly for the purpose intended. Can't thank you enough for your help. The calculations make much more sense now that I see them in a complex example.