Forum Discussion
Date Selection Formula Issue
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])
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,
2 Replies
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,
Great explanation, thanks so much for the help. This works great!
