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

CahabaData's avatar
CahabaData
Memorable Member
10 years ago

I would think you must create a new table: CustomQuarters  that consists of 2 columns (start date, end date) and 4 rows (one for each quarter) minimum

 

...and refer to this new table with <>= values in a calculated column to assign each record to their correct quarter; it could be done as a measure or a column though I tend to make it a new column and have it set up at the beginning - particularly if you are going to slice/refer to it alot.

 

if this is to work forever then you'll need to leave out the year in the new table and parse apart your dates.... or if it is okay to work within a span of years then it's not too much trouble to fill out the new table with as many quarters/years as you would need to work with.