Forum Discussion

BKnecht's avatar
BKnecht
Icon for Kudo Kingpin rankKudo 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

kcantor's avatar
kcantor
Icon for Community Champion rankCommunity Champion
10 years ago

BKnecht

When you are asking about adding a columns with numbers, have you tried using query editor, adding a custom column and selecting "Add Index Column". Of course that works on the assumption that you want to add that column to your date table. It is in the Add Column Tab at the top left.

BKnecht's avatar
BKnecht
Icon for Kudo Kingpin rankKudo 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.