Current Quarter,Last Quater,Next Quarter Text is not displaying

[Anonymous]

Hi All,

i'm try to display Current Quarter,Last Quater,Next Quarter Text in table but it is not displaying could please suggest,

Cal Year/Qtr = IF(FC[Cal year/Quarter] = FORMAT(TODAY(),"yyyy q"),"Current Quarter", IF(FC[Cal year/Quarter] = FORMAT(EOMONTH(TODAY(),-1),"yyyy q"),"Last Quarter", IF(FC[Cal year/Quarter] = FORMAT(EOMONTH(TODAY(),+1),"yyyy q"),"Next Quarter", IF(FC[Cal year/Quarter] = "Not assigned",FC[Cal year/Quarter],RIGHT(FC[Cal year/Quarter],4)+""+LEFT(FC[Cal year/Quarter],2)))))

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

According to the error message, the formula returns two different types of values. It's not supported for a single column to have different types of values in Power BI.

The first three if conditions return text-type data, but in the last if condition you are using “+”. You should use "&" to concatenate text instead of "+".

Cal Year/Qtr = IF(FC[Cal year/Quarter] = FORMAT(TODAY(),"yyyy q"),"Current Quarter", IF(FC[Cal year/Quarter] = FORMAT(EOMONTH(TODAY(),-1),"yyyy q"),"Last Quarter", IF(FC[Cal year/Quarter] = FORMAT(EOMONTH(TODAY(),+1),"yyyy q"),"Next Quarter", IF(FC[Cal year/Quarter] = "Not assigned",FC[Cal year/Quarter],RIGHT(FC[Cal year/Quarter],4)&""&LEFT(FC[Cal year/Quarter],2)))))

Best Regards,

Jay

[ALLUREAN]

Hi, @Anonymous 

Since I don't take a look into your data structure, can you try the below example, if not working please provide more info.

Cal Year/Qtr =
VAR CalYearQ = FC[Cal year/Quarter]
VAR CurrQ = FORMAT(TODAY(),"yyyy q")
VAR LastQ = FORMAT(EOMONTH(TODAY(),-1),"yyyy q")
CAR NextQ = FORMAT(EOMONTH(TODAY(),+1),"yyyy q")

SWITCH(TRUE(),

CalYearQ = CurrQ, "Current Quarter",
CalYearQ = LastQ, "Last Quarter",
CalYearQ = NextQ, "Next Quarter",
CalYearQ = Not assigned", CalYearQ,
RIGHT(CalYearQ,4)+""+LEFT(CalYearQ,2)))))