I'm trying to determine how Analysis Services DateDiff handles
Null/Empty values in a DateDiff function.

The Setup:
I've got a Measure that is calculated by performing a DateDiff on two
fields from my source fact table.

e.g.
The Measure "Duration" has a Source Column defined as:
DateDiff(d,"dbo"."tbl_Line_Item"."Date1",
"dbo"."tbl_Line_Item"."Date2")

Then I create a Calculated Measure that is an average:
Average Duration defined as:
[Measures].[Duration]/[Measures].[Order_Num]

with Order_Num being a unique ID for the record in the fact table,
with a Aggregate Function of "Count".

The Question:
I have many rows in my fact table that have a NULL value for Date2.
What does DateDiff return in this situation? 0? I've read that
Analysis Services uses 0 in place of NULL in several places and
I'm concerned that if DateDiff returns a 0 in this situation,
that it will skew my averages down for all the records where there
is a NULL/Empty value in Date2.

Anyone have an answer?

Thanks Much. Please respond to the group, this address is
used for posting to newsgroups only...

TT

I'm honing in on my answer, but I have a new problem now.

effectively what I did is equivalent to returning a O for
a datediff where one of the dates is null, as I was using
the line count as my denominator in my calculation. Therefore
the results were skewing due to the fact that *all* line items
were being counted regardless of whether there was a date
or not.

S0, what I need now is a line count for lines that don't have
a NULL for Date2.

I've tried creating a new Measure calculated as such:
iif(IsEmpty("dbo"."tbl_Line_Item"."Date2"),0,1)

I would then have this measure Sum, to give me the number
of rows that are not null for Date2...

however I'm getting a "The Column is not valid" error...

apparently I do

well it turns out I was over-thinking this... trying to
do an IsEmpty test on the Date2 field was overkill.

Created a Measure that was a count of the Date2 field,
which returned the number of non-null/non-empty values.
This number is then used as my denominator. It's actually
irrelevant whether the DateDiff returns 0 or not for a
comparison, as neither contributes to the numerator, and
therefore doesn't matter.

it seems simple now, but I'm still getting my mind around
this whole cube thing.

just thought I should post my results in case other newbs
out there are struggling with similar issues.

TT