Forum Discussion
Date calculations fail for some date column
Do you have a separate Calendar Table by a chance? If yes, by which date is it linked to your fact table?
Advocate Il Would suggest to check for
- Nulls and blanks: Verify that the NC Created Date column does not contain any blank or null values, as this may cause the DATEDIFF function to return BLANK().
- Ensure NC Created Date and NC Completed Date are the same data type and format.
rit_ty7thank you for your input. Created Date will never be null or blank because the record cannot be created without it. Completed Date does have blanks and it works as expected. I've checked the formatting for all four date columns and they all appear to be consistent. The first screenshot is from the Transform window where the date fields are initially created from a Date/Time field, they all show as data type Date. The second screenshot is from the table view, showing roughly the same thing. You'll notice that the far left columns are test columns and the code is included. Everything works when using the Created Date in creating columns. Here is the code for some of those columns
__TestAged =
VAR MaxDate = MAX('datekey'[date])
RETURN
IF(
[__TestStatus] = "open" &&
[__Test Created in RP] = "valid" &&
MaxDate - 'NC Main'[NC Created Date] > 60,
"aged",
"not aged"
)
__Test Created in RP = IF('NC Main'[NC Created Date]<=DATE(2024,11,24),"Valid","Not Valid")
AgataJHelper II
Do you have a separate Calendar Table by a chance? If yes, by which date is it linked to your fact table?
Yes, I have a date table 'DateKey' and 'nc main'[nc created date] is linked to 'datekey'[date]. there is a secondary link between [date] and [nc completed date].
AgataJfollowing your thought process I checked my relationships. Interestingly enough if I make the active connection inactive the measures work. I'm still not sure why this should create such chaos but at least I have a 'fix' for now.
Thankyou
- AgataJ
Hi Anonymous ,
I'm glad you fixed the issue, and I am confused a bit as well. It's quite tricky to use two dates in one DAX measurement. USERRELATIONSHIP might be useful for inactive relationship.
Kind regards,
Agata