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
Stharwani
Advocate I
Advocate I

Convert Period to Month name

Hello,

 

My period is formed of date 

FinPeriod = if(month('Date'[Date])>6,MONTH('Date'[Date])-6,month('Date'[Date])+6)

 

Is there a way to format the period number to MMM format, mainly for visulaisation purposes

 

finperiod.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks.

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @Stharwani,

After research, we unable to transfer number month to "MMM" format directly. You'd better create a calculated column using the formula.

NewPeriod=SWITCH([FinPeriod], 1, "Jan", 2, "Feb", 3, "Mar", 4, "Apr"  
               , 5, "May", 6, "Jun", 7, "Jul", 8, "Aug"  
               , 9, "Sep", 10, "Oct", 11, "Nov", 12, "Dec"  
               , "Unknown month number" )  


Then use the [NewPeriod] as axis level, to replace the period number.

Best Regards,
Angelia

View solution in original post

6 REPLIES 6
Phil_Seamark
Employee
Employee

HI @Stharwani

 

Please try this

 

Month Name = FORMAT(
            DATEVALUE("2018-" & FinPeriod & "-1") ,
            "MMM")

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

v-huizhn-msft
Employee
Employee

Hi @Stharwani,

After research, we unable to transfer number month to "MMM" format directly. You'd better create a calculated column using the formula.

NewPeriod=SWITCH([FinPeriod], 1, "Jan", 2, "Feb", 3, "Mar", 4, "Apr"  
               , 5, "May", 6, "Jun", 7, "Jul", 8, "Aug"  
               , 9, "Sep", 10, "Oct", 11, "Nov", 12, "Dec"  
               , "Unknown month number" )  


Then use the [NewPeriod] as axis level, to replace the period number.

Best Regards,
Angelia

@v-huizhn-msft

 

When I try to do this, I get an error at every name of the month. It says syntax is incorrect. Do you know why this?

Hi @BiBra Turns out Switch is only available for Excel 2016, and I am using 2013, so this wasn't working for me either.

 

I used an IF formula to look at the Period ID and then replace it with the month and hid the Period ID field, which worked for me.

 

good luck

@BiBra Hi - I got an error also,  turns out Switch function is only available in Excel 2016 - and I am using Excel 2013. I hope that helps.

 

I have used an IF formula that looks at the Period ID and then hidden the Period ID so it doesn't display, which has worked.

 

thanks

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.

Top Solution Authors
Top Kudoed Authors