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

That makes complete sense. My only problem is that I don't know how to create a calculated column that just gives me ordered numbers starting at 1 and then does down haha. Any ideas? Seems simple to me but I can't think of a way to do it with DAX. 

 

Thank you!

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)