creating quarters from Period

[Switto]

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)))

Date	Period
02 July 2021	P12
01 July 2021	P12
30 June 2021	P12
29 June 2021	P12
28 June 2021	P12

Request your help on this.

Thanks

[amitchandak · Accepted solution]

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

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

Thanks for the input.

[amitchandak]

Thanks for the update. Can you accept a solution?

[Switto]

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."