cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stharwani Regular Visitor
Regular Visitor

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

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Convert Period to Month name

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

6 REPLIES 6
v-huizhn-msft Super Contributor
Super Contributor

Re: Convert Period to Month name

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

Highlighted
Stharwani Regular Visitor
Regular Visitor

Re: Convert Period to Month name

Thank you.

BiBra Member
Member

Re: Convert Period to Month name

@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?

pearlatine Frequent Visitor
Frequent Visitor

Re: Convert Period to Month name

@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

pearlatine Frequent Visitor
Frequent Visitor

Re: Convert Period to Month name

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

Phil_Seamark Super Contributor
Super Contributor

Re: Convert Period to Month name

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!