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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
CMSINFO
Frequent Visitor

Date Format

Hi, I am new to Power BI, can anyone tell me how to add dates in the format JAN17, FEB17,MAR17? I don't see this format on Modeling -> Format.

 

Thanks!

2 ACCEPTED SOLUTIONS
ChrisMendoza
Resident Rockstar
Resident Rockstar

@CMSINFO -

 

As a calculated column you can format the date as:

 

Column = FORMAT(Table1[Date],"MMMYY")





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

PattemManohar
Community Champion
Community Champion

@CMSINFO  You can convert the date fields using FORMAT function. Try adding a new column as below

 

DateMMMYY = FORMAT(TableName[DateColumn],"MMMYY")




Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

8 REPLIES 8
PattemManohar
Community Champion
Community Champion

@CMSINFO  You can convert the date fields using FORMAT function. Try adding a new column as below

 

DateMMMYY = FORMAT(TableName[DateColumn],"MMMYY")




Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Thanks! It worked however now the columns are in alphabetical order not on cronological order, how to sort it out?

@CMSINFO Add an another new column (if table doesn't have one as below) and call it as SortKey

 

SortKey = FORMAT(Table[DateColumn],"YYYYMM")

 

Select the field that you want to sort on (in this case it is the field that you have created earlier i.e. MMMYY format) then click on "Sort By Column" option under "Modelling" tab use SortKey field

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




It sorted the column in the Data but my report remains in alphabetical order, why?

@CMSINFO  For your reference...

 

image.pngimage.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




 

The months on my matrix still in alphabetical order...image.png

 

@CMSINFO -

 

Is your calculated column [MMMYY] in the 'Date' Table (a proper date table) and the fact table with measures/etc. related to that 'Date' Table? Or are you trying to do all this within the fact table?

 

I'm guessing you need to share more of your model cause what's been suggested should work.

 

7.PNG

 

Fact Table

 

8.PNG

9.PNG

 

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



ChrisMendoza
Resident Rockstar
Resident Rockstar

@CMSINFO -

 

As a calculated column you can format the date as:

 

Column = FORMAT(Table1[Date],"MMMYY")





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.