Forum Discussion

Stharwani's avatar
Stharwani
Icon for Advocate II rankAdvocate II
9 years ago
Solved

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks.

Need Help
Show and Tell
Tips and Tricks
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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

    • BiBra's avatar
      BiBra
      Icon for Helper III rankHelper III
      8 years ago

      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's avatar
        pearlatine
        New Member
        8 years ago

        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