Forum Discussion

Stharwani's avatar
Stharwani
Advocate 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 vis...
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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft 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

  • Stharwani's avatar
    Stharwani
    Advocate II
    9 years ago

    Thank you.

  • BiBra's avatar
    BiBra
    Helper 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

    • pearlatine's avatar
      pearlatine
      New Member
      8 years ago

      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