QTD Function with Custom Fiscal Quarters

[BKnecht]

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 create YTD measures, I can choose my Year End Date, which works well.

However, with the DatesQTD Function, I can't choose a quarter start/end date. I tried creating a measure to compensate, by using the DatesQTD function, and then a DateAdd with adding 3 months:

Won Bookings QTD = CALCULATE([Won Bookings],DATESQTD('Date Table'[Date]),DATEADD('Date Table'[Date],3,MONTH))

But, I get the error below when trying to display this in any type of graphic.

Anyone have any ideas or workarounds? Or am I doing something wrong? Any help would be much appreciated!

[v-caliao-msft · Accepted solution]

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]

Hi Charlie,

This is exactly what I was looking for. some of the other posts worked as well, but this gives me a way to slice and display the data in the way I was hoping to do.

Thank you for your reply and taking the time, huge help! I'm working on building a Dashboard for our Finance Team, and this will be extremely helpful.

Thank you again!

[CahabaData]

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.

[Anonymous]

There is likely a 'correct way' to do this, using built in functions.  But, I typically don't do it that way, maybe cuz I'm silly.

The easy way to do this is have a calculated column in your calendar table that is the QuarterNumber -- that always increases even across years.    Then just write the measure using that calc column... something like:

Won Bookings QTD = CALCULATE([Won Bookings], FILTER(ALL('Date Table'), 'Date Table'[Date] <= MAX('Date Table'[Date]) && 'Date Table'[QuarterNumber] = MAX('Date Table'[QuarterNumber])))

(Alternatively, you could have a Quarter number that does reset each year... and include Year in your measure).

[Tom216]

Worked Perfectly...Thanks!

[BKnecht]

Hey Scottsen,

Thanks for the reply. One thing I'm not understanding is the QuarterNumber calculated column - would that just be a column that runs through whole numbers, starting at 1, then 2, then 3, etc?

[Anonymous]

Yep!

[BKnecht]

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]

@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]

@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]

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)