Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
leondnz
New Member

DAX - Date variable with blanks

I will try to explain in the best way possible what I need. So basically I have a calculated column from two other date columns, the "Off Rail" and "Rail Arrived", and like the dates of these columns have to respect an order, the off rail must happen before the rail arrived. The new columns must do these two checks:

 

1) Check if the Off Rail date happened before the Rail Arrived

2) Check if one of those values is BLANK

3) I'm having an issue regarding when these two dates are the same because if they are the same it's OK, but my formula does not return it, I wonder if the problem is at the less than or equal operator when I compare the dates between these two columns 

 

I've created a formula, but I'm not sure if it is in the proper context. I'd appreciate it if someone could take a look at it:

 

RAIL ARVD > OFF RAIL =

VAR Days = IF(

         NOT(ISBLANK(Plan[Rail Arrived])),
         DATEDIFF(Plan[Off Rail], Plan[Rail Arrived]DAY),
         BLANK()

         )

VAR SEBLANK = IF(NOT(ISBLANK(Days)), "OK""CHECK")

VAR SEDATE = IF(Days <= 0"CHECK""OK")

VAR FINAL = IF(SEBLANK = SEDATE"OK""CHECK")

RETURN

FINAL
1 ACCEPTED SOLUTION

So I tried but it still didn't solve the problem of the same dates, but your idea helped me to implement this new formula, I run some tests and apparently it is working now, thanks for the help @tamerj1 

 

RAIL ARVD > OFF RAIL =

VAR SEBLANK = IF(
                ISBLANK(Plan[Rail Arrived]), "CHECK",
                "OK"
                )

VAR SEDATA = IF(
                INT(DATEDIFF(Plan[Rail Arrived], Plan[Off Rail],DAY)) > 1, "CHECK",
                "OK"
               )

VAR FINAL = IF(
                OR(SEBLANK = "CHECK", SEDATA = "CHECK"),
                "CHECK", "OK"
              )
                 
RETURN
FINAL

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @leondnz 

please try

=
IF (
Plan[Rail Arrived] = BLANK (),
"Check",
IF ( INT ( Plan[Off Rail] - Plan[Rail Arrived] ) >= 1, "Ok", "Check" )
)

So I tried but it still didn't solve the problem of the same dates, but your idea helped me to implement this new formula, I run some tests and apparently it is working now, thanks for the help @tamerj1 

 

RAIL ARVD > OFF RAIL =

VAR SEBLANK = IF(
                ISBLANK(Plan[Rail Arrived]), "CHECK",
                "OK"
                )

VAR SEDATA = IF(
                INT(DATEDIFF(Plan[Rail Arrived], Plan[Off Rail],DAY)) > 1, "CHECK",
                "OK"
               )

VAR FINAL = IF(
                OR(SEBLANK = "CHECK", SEDATA = "CHECK"),
                "CHECK", "OK"
              )
                 
RETURN
FINAL

Oh thanks, I'll try it

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.