Date Selection Formula Issue

[Mobleyjk]

Hi All,

I'm having and odd issue with a date selection calculation, as described below:

I have two date fields, Created Date and Hub Activation, both of which are formatted the same. I've created a separate date table used to calculate the date +(any amount) of business days, excluding holidays. When I enter Hub Activation into the formula as the reference to look up the business days value, the formula works, however when I enter any other date (Including Created Date) into the formula, it stops working. Am I missing something here? Is this some sort of bug?

Formula: Project Calc = MINX(filter('Calendar','Case'[Created Date] = 'Calendar'[Date]), 'Calendar'[+29])

[DataNinja777 · Accepted solution]

Hi @Mobleyjk ,

The issue you're encountering is caused by a data type mismatch between your columns. Your 'Case'[Created Date] field is a Date/Time type, meaning it stores both a date and a specific time of day. In contrast, your 'Calendar'[Date] column is a pure Date type, where the time is implicitly set to midnight (12:00:00 AM). Your formula fails because a date with a time like 10/21/2025 09:30:15 AM is not equal to 10/21/2025 12:00:00 AM, so the FILTER function returns an empty table.

To resolve this, you must remove the time portion from the [Created Date] before comparing it. You can do this by using the TRUNC function. Your original formula can be corrected to work as follows:

Project Calc =
MINX (
    FILTER (
        'Calendar',
        TRUNC ( 'Case'[Created Date] ) = 'Calendar'[Date]
    ),
    'Calendar'[+29]
)

While the above formula is now correct, a more efficient and readable best practice in DAX is to use the LOOKUPVALUE function for this task. It's specifically designed to retrieve a value from a column when a key in another column matches your specified value. This avoids iterating over the entire calendar table with FILTER and results in a cleaner, faster calculation.

Project Calc =
LOOKUPVALUE (
    'Calendar'[+29],
    'Calendar'[Date], TRUNC ( 'Case'[Created Date] )
)

Best regards,

[Mobleyjk]

Great explanation, thanks so much for the help. This works great!