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

BKnecht's avatar
BKnecht
Kudo Kingpin
10 years ago

kcantor - that's exactly what I was going to do, but my DAte Table isn't a query, I used Calendar Auto to create a New Table in my PBXIX File.

Anonymous's avatar
Anonymous
Not applicable
10 years ago

just some maths :)   If you have a MonthNum column (that is just 1-12 repeating) then something like:

 =ROUNDUP(Calendar[MonthNum] / 4) + 4 * (YEAR(Calendar[Date]) - 2015)