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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
pmdave
New Member

IF Statement evaluating dates is probably working great, but not like I think it should

Here is some low-hanging fruit for someone...but would be a great help to end my frustration.

 

Pulling data in from an Oracle Database to Power BI.  I need to ignore a bug in our Oracle Application that will at times write in a date of 01/01/4019 in the data.  Went to Oracle to get this fixed and they told me they have it filtered out from the UI of the application, so I could go kick rocks, which is another reason why Oracle sucks.  Moving on, the Field format in Power BI is mm/dd/yyyy if that matters.  When that value appears in the Oracle data, I don't want it to appear in our reports in Power BI.  So, I created a new column and turned to my simple IF statement that will substitute null for any values of January 1, 4019 that may come in, and will write in the date correctly if it is in the next 1,996 years:

 
RevisedDate = IF('Activity Updates'[Date] = 01/01/4019BLANK(), 'Activity Updates'[Date])
 
It in essence returns a replica of the Date column, and doesn't seem to recognize 1/1/4019 as a date value no matter what format I enter the date in the IF statement.  Until they add the "read my mind please" functionality to Power BI, does anyone know what I have done wrong and why all resulting 1/1/4019 dates are still appearing in my handy "RevisedDate" column still rather than nulls?  Thanks in advance!
1 ACCEPTED SOLUTION
ERD
Super User
Super User

Hi @pmdave , here you are:

RevisedDate =
IF (
    'Activity Updates'[Date] = DATE ( 4019, 01, 01 ),
    BLANK (),
    'Activity Updates'[Date]
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

4 REPLIES 4
ERD
Super User
Super User

Hi @pmdave , here you are:

RevisedDate =
IF (
    'Activity Updates'[Date] = DATE ( 4019, 01, 01 ),
    BLANK (),
    'Activity Updates'[Date]
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

ERD,

 

Thank you so much!  That fixed it!  I had tried using the DATE and DATESBETWEEN statements, but I know my syntax was much different trying to get it to work.  Much appreciated!!!

Marianna
Frequent Visitor

Your date should be in quotation marks - try it out and let me know if it works 🙂

Thank you for your response Marianna!

 

This is one of the reasons I am confused.  If I put single quotes around the date, Power BI thinks it is a table (understandable) and gives the error "Cannot find table '01/01/4019' ".

 
If I put the 01/01/4019 in double quotes, it sees it as Text, and errors saying that it cannot compare text to date fields.
 
I did that, and have also tried different date formats (01-Jan-4019, 01-01-4019, etc.), and those don't seem to work.  Feels like this is such a fundamental problem that I am missing...it's a simple IF statement...but I'm stumped.  Again, thank you for your response!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.