Forum Discussion
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 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!
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
11 Replies
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).
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
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!
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.
