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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Denskim123
Regular Visitor

strange problem - date format is no longer being recognized because of "/" in my formula

Hi community,

 

I'm encountering a strange problem that I have not encountered before. I created a new measure/column using the formula shown below combining the 2 columns 'Month1' and 'Year1' to get the column 'MO/YR'. I also set this column format to be a date and it has been working great. 

 

Denskim123_0-1713809364414.pngDenskim123_2-1713809426779.png

 

Today, after refreshing my data (as I typically do), all of a sudden PowerBI is no longer recognizing that column as a date format and is throwing this error message. I can't figure out why all of a sudden it's saying the "/" is an issue???

 

Denskim123_3-1713809733187.png

 

Does anyone know why this might be happening and what/how I can correct it?  Thanks a million.

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

I think, like Greg, we have to hypothesise that the new data is causing a problem.
Are the Year1 and Month1 columns number type?

Why don't you comment out the code and put this in the calculated column:

DATE ( 'Raw Data'[Year1], 'Raw Data'[Month1], 1 )

The way it's currently written involves some conversions behind-the-scenes which may be prone to error.

This new code might throw an error but it may allow you to narrow it down.

 

View solution in original post

4 REPLIES 4
HotChilli
Super User
Super User

I think, like Greg, we have to hypothesise that the new data is causing a problem.
Are the Year1 and Month1 columns number type?

Why don't you comment out the code and put this in the calculated column:

DATE ( 'Raw Data'[Year1], 'Raw Data'[Month1], 1 )

The way it's currently written involves some conversions behind-the-scenes which may be prone to error.

This new code might throw an error but it may allow you to narrow it down.

 

@HotChilli your suggestion worked great! i went back and double checked my columns but didn't find anything out of place. i'm still baffled. in anycase, thanks for your alternative solution!

Greg_Deckler
Super User
Super User

@Denskim123 My guess is that you literally have a "/" in your data for some reason as if your Year1 and Month1 columns for a particular row are null.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I checked my columns for a null value or to see if any cell contained "/" but nothing. columsn are clean and only has the values that I expect to see. I'm totally baffled as to why this is happening all of a sudden....

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors