Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have the following table called 'In Out Dates' that looks something like this:
Driver Name | Driver Number | In Date | Out Date |
A B | 111111 | 08 July 2023 | 04 August 2023 |
A B | 111111 | 08 September 2023 | 19 October 2023 |
A B | 111111 | 05 November 2023 | |
A W | 222222 | 08 July 2023 | 24 August 2023 |
A W | 222222 | 08 October 2023 | |
A D | 333333 | 08 July 2023 |
I have a DAX measure that is the following:
In Out Status =
VAR TodayDate = [Today's Date]
VAR SelectedDriver = SELECTEDVALUE('Drivers List'[Driver Number])
VAR InDate = CALCULATE(MAX('In Out Dates'[In Date]), 'In Out Dates'[Driver Number]) = SelectedDriver
VAR OutDate = CALCULATE(MAX('In Out Dates'[Out Date]), 'In Out Dates'[Driver Number]) = SelectedDriver
RETURN
IF(
AND(
TodayDate >= InDate,
(OutDate = BLANK() || TodayDate < OutDate)
),
"In",
"Out"
)
The measure must reference in In Out Dates table, take the driver number the user has selected in a slicer (SelectedDriver) and use this to get the latest date from the In Date column and the latest date from the Out Date column. If today's date is greater than or equal to the In Date and either the Out Date is null or today's date is less that the Out Date then the label In should be used, otherwise Out should be used.
The Today's Date measure is simply -
Today's Date = TODAY()
Running this however, I get an error in the visual and clicking on it it tells me 'Can't load the data for this visual - DAX comparison operations do not support comparing values of type Date with values of type Text'.
This is odd as the data types in the tables are set to date for In Date and Out Date columns and the Driver Number is text data type.
What am I doing wrong?
Your measure can be simplified.
@lbendlin It doesn't quite give me what I want.
Based on the data in my orginal table above, if the current date is 24/08/2023, driver 222222 should have status as "Out" because the Out Date is 24/08/2023 and this would be the case until the next date - In Date of 08/10/2023.
if the current date is 24/08/2023
yeah, but that ship has sailed. Feels like your requirement is slightly different from what you describe?
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |