Forum Discussion

BKnecht's avatar
BKnecht
Kudo Kingpin
10 years ago
Solved

QTD Function with Custom Fiscal Quarters

Hi,   I'm trying to create a Quarter to Date measure, but we have custom fiscal quarters, with our fiscal year starting in April (so, Q1 would be April-June). When I use the DatesYTD Function to cr...
  • v-caliao-msft's avatar
    v-caliao-msft
    10 years ago

    Hi BKnecht,

     

    You can create a calculated column to return fiscal quarter, the sample DAX would like below.
    FYQuarter = "FY "&IF(MONTH(FiscalQTD[Date])>=4,YEAR(FiscalQTD[Date])+1,YEAR(FiscalQTD[Date]))&" Quarter"&CEILING(IF(MONTH(FiscalQTD[Date])>=4,(MONTH(FiscalQTD[Date])-3)/3,(MONTH(FiscalQTD[Date])+9)/3),1)

    The create the QTD column use the DAX below.
    QTD = CALCULATE(SUM(FiscalQTD[Sales]),FILTER(ALLEXCEPT(FiscalQTD,FiscalQTD[FYQuarter]),FiscalQTD[Date]<=EARLIER(FiscalQTD[Date])))

     

    The report looks like below.

     

    Regards,

    Charlie Liao

QAPowerBITim's avatar
QAPowerBITim
Regular Visitor
9 years ago

Hi,

 

I am attemping to change the formula below and am having issues. Any help would be greatly appreciated. The output I am looking for is:

 

FY 2017 Quarter 3

Jan

Feb

Mar

FY 2017 Quarter 4

Apl

May

June

FY 2018 Quarter 1

July

Aug

Sept

FY 2018 Quarter 2

Oct

Nov

Dec

 

Any ideas what needs to change?