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
wnicholl
Resolver II
Resolver II

Converted Date Field (Incorrect Day Showing)

I converted source data field (yyyymmdd) "ACEFDT" using the following formula to create a calculated column to show (mm/dd/yyyy).  The calculated column show the year and month as being corrcet, but the day is not corrcet.  I'm not sure if it's my calculation or how the source field is structured. Any help would be great. Thanks in advance!

 

Formula:  Effective Date = DATE(LEFT(AISCVGP[ACEFDT],4),MID(AISCVGP[ACEFDT],5,2),LEFT(AISCVGP[ACEFDT],2))

 

Source Date Field (ACEFDT)

Source data field "ACEFDT" Format and Data Type are set as "Text"Source data field "ACEFDT" Format and Data Type are set as "Text"

 

Calculated Column Field (Effective Date) 

Calculated column showing incorrcet day. The year and month are corrcet..PNG

1 ACCEPTED SOLUTION

I was able to resolve my date issue. The below process was  used in power query. Thanks again for your insights. 

 

1) Changed source field to TEXT
2) Created a custom column and used M-code // Text.BeforeDelimiter([ACEFDT], " ")) //
3) Changed new column to Date format

View solution in original post

7 REPLIES 7
grantsamborn
Solution Sage
Solution Sage

Try this:

Effective Date = DATE(LEFT(AISCVGP[ACEFDT],4),MID(AISCVGP[ACEFDT],5,2),RIGHT(AISCVGP[ACEFDT],2))

 

 

Thanks for the reply.  Unfortunately the formaula is not working for me. 

 

Error.PNG

Hi @wnicholl 

Sorry about that.  How about this?

 

Effective Date = 
DATE(
	VALUE( LEFT( AISCVGP[ACEFDT], 4) ),
	VALUE( MID( AISCVGP[ACEFDT], 5, 2) ),
	VALUE( RIGHT( AISCVGP[ACEFDT], 2) )
)

 

I was able to resolve my date issue. The below process was  used in power query. Thanks again for your insights. 

 

1) Changed source field to TEXT
2) Created a custom column and used M-code // Text.BeforeDelimiter([ACEFDT], " ")) //
3) Changed new column to Date format

HotChilli
Super User
Super User

The formula is incorrect. Check the last section.

However, this will convert to a date in Power Query simply by changing the type (i.e. without the convoluted formula) 

I was able to resolve my date issue. The below process was  used in power query. Thanks again for your insights. 

 

1) Changed source field to TEXT
2) Created a custom column and used M-code // Text.BeforeDelimiter([ACEFDT], " ")) //
3) Changed new column to Date format

Thanks for the reply. I'm entirely sure what the best method is to convert the date field in power query, but I tried to convert the field to a text then changing back to a date field in power query and that's did not work for me.  

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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