Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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"
Calculated Column Field (Effective Date)
Solved! Go to 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
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.
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
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.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
93 | |
60 | |
43 | |
35 | |
34 |