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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
revansh
Helper IV
Helper IV

Date Format

Hi Everyone,

 

               I have a line graph with month start date on X-axis. 

 

Month start date format = MM-YYYY (i.e. JAN2017 , FEB2017, MAR2017).

 

I want to change the format to MM-YY (ie JAN17, FEB17,MAR17 etc..) which is not available in the data formats list.

 

how can i get this format with correct sorting.

 

Thanks

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@revansh

 

Create 2 columns (1 to display and to sort it)

 

Custom Column = FORMAT('Calendar'[Date], "MMM YY")

Custom Column Sort Order = 
INT (
    CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( 'Calendar'[Date], "MM" ) )
)

Then Select the Custom Column => Modeling Tab => Sort Column By => select the Custom Column Sort Order column

 

Sort Column By.png

 

That should do it.

Good Luck! Smiley Happy

View solution in original post

4 REPLIES 4
Sean
Community Champion
Community Champion

@revansh

 

Create 2 columns (1 to display and to sort it)

 

Custom Column = FORMAT('Calendar'[Date], "MMM YY")

Custom Column Sort Order = 
INT (
    CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( 'Calendar'[Date], "MM" ) )
)

Then Select the Custom Column => Modeling Tab => Sort Column By => select the Custom Column Sort Order column

 

Sort Column By.png

 

That should do it.

Good Luck! Smiley Happy

Thank you Sean. That worked for me also. Any idea how we can do it for a YTD chart?

 

I am using a measure that use monthly sales cost and date to get YTD for each month. Something like this.

 

YTD = TOTALYTD(SUM(Sales[Cost]),Sales[Date]) 

 

But when I use, Date(MMM YYYY) = FORMAT(Sales[Date],"MMM YYYY") in the charts, it messes up my YTD chart.

 

Any ideas how to can we make it work?

 

Thanks!

 

 

Anonymous
Not applicable

select the column and go to column tools.

Select the data type and set it as "Date"

select format and type as mmm-yy

 

It worked.

 

Thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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