cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vtomsons
Frequent Visitor

Date Change from DD-MM-YYYY

I have one data source giving me trouble because it is DD-MM-YYYY formatted. When I change the data from text to date, Power BI is not consistent with numbers in the day or month column below 12. I need to have the month data accurate so I can define a Fiscal year starting in May using the syntax below.

 

Can anyone help ensure my date can transfer the month data accurately?

 

Fiscal Year = IF(MONTH('NC Reg''n'[Current Registration Status Date])>4,YEAR('NC Reg''n'[Current Registration Status Date])+1,YEAR('NC Reg''n'[Current Registration Status Date]))

 

Thanks,

 

Val

1 ACCEPTED SOLUTION

Accepted Solutions
Vtomsons
Frequent Visitor

Re: Date Change from DD-MM-YYYY

Thank you very much. My data is a little messed up right now after trying a few ways. I'll try your suggestion and let you know how it goes in the next few days.

 

Thanks so much for your time and advice.

 

Val

View solution in original post

6 REPLIES 6
Super User I
Super User I

Re: Date Change from DD-MM-YYYY

@Vtomsons 

 

Try changing the date to MM/dd/yyyy format if you could.

 

In Query Editor,Right Click in the column -  Change Type-Using Locale - Select the Type (Date Time) and Select the Locale.

 

If this helps, mark it as a solution

Kudos are nice too

 

Connect on LinkedIn
Vtomsons
Frequent Visitor

Re: Date Change from DD-MM-YYYY

Thanks but it didn't change anything. 

 

Should I use some sort of Table? I really don't care for the exact day but I do want the month and year to be accurate.

 

Any other suggestions?

 

Val

Super User IV
Super User IV

Re: Date Change from DD-MM-YYYY

Try create a column like 

new date =

mid(DD__MM__YY[Date],4,2) &"/"& left(DD__MM__YY[Date],2) & "/" & right(DD__MM__YY[Date],4)
 
join it with date table
 
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Super User I
Super User I

Re: Date Change from DD-MM-YYYY

@Vtomsons 

 

Did you choose datatype as Date and United Kingdom(English). They follow dd/mm/yyyy format.

After that you could use Month or Year Dax function.

Cap11.PNG

Cap11.PNG

 

After that it will behave as regular date.

 

 

If this helps mark it as a solution

Kudos are nice too

Connect on LinkedIn
Vtomsons
Frequent Visitor

Re: Date Change from DD-MM-YYYY

Thank you very much. My data is a little messed up right now after trying a few ways. I'll try your suggestion and let you know how it goes in the next few days.

 

Thanks so much for your time and advice.

 

Val

View solution in original post

Vtomsons
Frequent Visitor

Re: Date Change from DD-MM-YYYY

Looks like the 2nd solution worked. I was not linking to UK English like you suggested. 

 

Thanks a ton.

 

Val

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors