Date calculations fail for some date column

[boyddt_mn]

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

__Test DateDiff =

VAR MaxDate = DATE(2024,11,24)

RETURN

CALCULATE(

    DISTINCTCOUNT('NC Main'[NC Document Number]),

    FILTER(

        'NC Main',

        DATEDIFF('NC Main'[NC Created Date], MaxDate, DAY) > 60

    )

)

Column Calculation that works

__Test Aged =

 VAR MaxDate = DATE(2024,11,24)

 RETURN

 IF(

    ('NC Main'[NC Completed Date] > MaxDate ||

    ISBLANK('NC Main'[NC Completed Date])

    ) &&

    'NC Main'[NC Created Date] <= MaxDate &&

    DATEDIFF('NC Main'[NC Created Date], MaxDate, DAY) > 60,

    "Aged",

    "Not Aged"

)

[rit_ty7]

@boyddt_mn 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

[rit_ty7]

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.

[boyddt_mn]

@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")

[AgataJ · Accepted solution]

Do you have a separate Calendar Table by a chance? If yes, by which date is it linked to your fact table?

[boyddt_mn]

@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 @boyddt_mn ,

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

[boyddt_mn]

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].