Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Fiscal quarter calculation bug

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"))))
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

2 Replies

  • XLBob's avatar
    XLBob
    Resolver II
    7 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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.