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

kcantor's avatar
kcantor
Community 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
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)