Forum Discussion
Date calculations fail for some date column
I have the following measure that works if I choose 'NC Main'[NC Completed Date] yet fails when I substitute 'NC Main'[NC Created Date]. The Created Date field just produces (BLANK). The two columns are identical in terms of formats and data type. I have tried not using DATEDIFF and just subtracting the dates and I get the same results; Completed works and Created gives (BLANK). I'm trying to figure out how to get a model assembled without PII or propriatary info. I have created multiple colums that breaks this measure down into smaller bits and all of those work on Created Date.
Does any one have any ideas where I can start troubleshooting?
Measure that is hosed
Column Calculation that works
Do you have a separate Calendar Table by a chance? If yes, by which date is it linked to your fact table?
7 Replies
rit_ty7Advocate I
l 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?
- rit_ty7
Anonymous Hello,
Maybe try the below code:
__TestAged =
VAR MaxDate = CALCULATE(MAX('datekey'[date]))
RETURN
IF(
[__TestStatus] = "open" &&
[__Test Created in RP] = "Valid" &&
NOT ISBLANK('NC Main'[NC Created Date]) &&
DATEDIFF('NC Main'[NC Created Date], MaxDate, DAY) > 60,
"aged",
"not aged"
)
where,
NOT ISBLANK('NC Main'[NC Created Date]): Prevents errors caused by blank dates