Fiscal quarter calculation bug

[Anonymous]

I am running PowerBI Desktop version 2.71.5523.821 64-bit. I use the following query below to appropriately name my values. The interesting thing here is any datetime value between 10/1/2018 to 12/30/2018 shows Q2FY18 except for 12/31/2018 shows Q2FY19.

Is there something wrong with my query? I created a new column to show me the month number and it looks right.

MonthNumber = month(incident_sla[taskslatable_end_time].[Date])

Fiscal_Quarter = if((Month(incident_sla[taskslatable_end_time].[Date])=1||Month(incident_sla[taskslatable_end_time].[Date])=2||Month(incident_sla[taskslatable_end_time].[Date])=3),"Q3FY"&RIGHT(YEAR(incident_sla[taskslatable_end_time].[Date]),2),IF((Month(incident_sla[taskslatable_end_time].[Date])=4||Month(incident_sla[taskslatable_end_time].[Date])=5||Month(incident_sla[taskslatable_end_time].[Date])=6),"Q4FY"&RIGHT(YEAR(incident_sla[taskslatable_end_time].[Date]),2),IF((Month(incident_sla[taskslatable_end_time].[Date])=7||Month(incident_sla[taskslatable_end_time].[Date])=8||Month(incident_sla[taskslatable_end_time].[Date])=9),"Q1FY"&RIGHT(YEAR(incident_sla[taskslatable_end_time].[Date]+1),2),IF((Month(incident_sla[taskslatable_end_time].[Date])=10||Month(incident_sla[taskslatable_end_time].[Date])=11||Month(incident_sla[taskslatable_end_time].[Date])=12),"Q2FY"&RIGHT(YEAR(incident_sla[taskslatable_end_time].[Date]+1),2),"Not sure"))))

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=IF(MONTH(incident_sla[taskslatable_end_time])<=3,"Q3FY"&RIGHT(YEAR(incident_sla[taskslatable_end_time]])),IF(MONTH(incident_sla[taskslatable_end_time])<=6,"Q4FY"&RIGHT(YEAR(incident_sla[taskslatable_end_time]])),IF(MONTH(incident_sla[taskslatable_end_time])<=9,"Q1FY"&RIGHT(YEAR(incident_sla[taskslatable_end_time]])+1),"Q2FY"&RIGHT(YEAR(incident_sla[taskslatable_end_time]])+1))))

Hope this helps.

[XLBob]

For 31 Dec 2018 below formula will give you 2019 becasue (31-Dec-2018)+1=1-Jan-2019

YEAR(incident_sla[taskslatable_end_time].[Date]+1),