Forum Discussion

Switto's avatar
Switto
Icon for Helper IV rankHelper IV
5 years ago
Solved

creating quarters from Period

Hi All,

 

I am trying to get the Quater no. from my calendar table.

 

I am using below formula but getting error, stating "Cannot convert value 'P2' of type Text to type True/False.".

 

FY QTR = 'EY calendar'[Date].[Year]&"-Q"&IF('EY calendar'[Period]="P1"||"P2"||"P3",1,'EY calendar'[Date].[Year]&"-Q"&IF('EY calendar'[Period]="P4"||"P5"||"P6",2,'EY calendar'[Date].[Year]&"-Q"&IF('EY calendar'[Period]="P7"||"P8"||"P9",3,4)))

 

DatePeriod
02 July 2021P12
01 July 2021P12
30 June 2021P12
29 June 2021P12
28 June 2021P12

 

Request your help on this.

Thanks

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Switto , Try like

     

    FY QTR = 'EY calendar'[Date].[Year]&"-Q"&IF('EY calendar'[Period] in {"P1","P2","P3"},1,'EY calendar'[Date].[Year]&"-Q"&IF('EY calendar'[Period] in {"P4","P5","P6"},2,'EY calendar'[Date].[Year]&"-Q"&IF('EY calendar'[Period] in {"P7","P8","P9"},3,4)))

     

    Better try Switch

    FY QTR = 'EY calendar'[Date].[Year]&"-Q"& 
Switch( true() , 
'EY calendar'[Period] in {"P1","P2","P3"},1,
'EY calendar'[Period] in {"P4","P5","P6"},2,
'EY calendar'[Period] in {"P7","P8","P9"},3,4)

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Switto , Try like

     

    FY QTR = 'EY calendar'[Date].[Year]&"-Q"&IF('EY calendar'[Period] in {"P1","P2","P3"},1,'EY calendar'[Date].[Year]&"-Q"&IF('EY calendar'[Period] in {"P4","P5","P6"},2,'EY calendar'[Date].[Year]&"-Q"&IF('EY calendar'[Period] in {"P7","P8","P9"},3,4)))

     

    Better try Switch

    FY QTR = 'EY calendar'[Date].[Year]&"-Q"& 
Switch( true() , 
'EY calendar'[Period] in {"P1","P2","P3"},1,
'EY calendar'[Period] in {"P4","P5","P6"},2,
'EY calendar'[Period] in {"P7","P8","P9"},3,4)

     

    • Switto's avatar
      Switto
      Icon for Helper IV rankHelper IV
      5 years ago

      Thanks Amit, It worked. I have changed the column FY QTR to text and it worked.

      Thanks for the input.

    • Switto's avatar
      Switto
      Icon for Helper IV rankHelper IV
      5 years ago

      Thanks for the quick reply. However, It showing different error. 
      "Function 'CONTAINSROW' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."