Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am trying to take a date field I have and rearrange it. Currently, it displays as either YYMMDD or YMMDD. If the year was 2008 or 2009, the 0 out front does not show in the year. To fix this I have created a new column I thought would work, but I am getting the error "DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values." So I added the VALUE function to convert my text to a number. Still no luck. I have tried with my column 'APLUS8FLV.CUSMS'[Cutsomer Added Date] formatted as decimal number and whole number. Here is my function:
Solved! Go to Solution.
Here's the final table (did this in Power Query):
This does make the assumption that all the dates takes in the year 2000 or beyond. Didnt see a way to diffentiate the year from your sample.
Here's a rundown what is going on in Power Query ( be sure to look at the applied steps! 😞
Text.PadStart( Text.From( [DisplayDate]) , if Text.Length( Text.From([DisplayDate]) )>5 then 0 else Text.Length( Text.From([DisplayDate]) ) +1 , "0")basically says that if the original display date is longer than 5 characters, dont do anything. If it's less then 5 characters, add a zero in the front
#date( [Year] , [Month], [Day] )
Here's the excel file:
Could you post some sample data and the expected output?
For example, if the customer was added on 02/22/19 or 03/20/2008, it would display as 190222 or 80320 respectively.
I would like for them to display as a date column, but the missing '0' is really messing me up.
Here's the final table (did this in Power Query):
This does make the assumption that all the dates takes in the year 2000 or beyond. Didnt see a way to diffentiate the year from your sample.
Here's a rundown what is going on in Power Query ( be sure to look at the applied steps! 😞
Text.PadStart( Text.From( [DisplayDate]) , if Text.Length( Text.From([DisplayDate]) )>5 then 0 else Text.Length( Text.From([DisplayDate]) ) +1 , "0")basically says that if the original display date is longer than 5 characters, dont do anything. If it's less then 5 characters, add a zero in the front
#date( [Year] , [Month], [Day] )
Here's the excel file:
Wow you make it look so easy!! Thank you very much!
Glad it helped!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |