cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## 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!

1 ACCEPTED SOLUTION
Employee

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 11
Employee

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

Helper II

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!

Memorable Member

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
Not applicable

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).

Frequent Visitor

Worked Perfectly...Thanks!

Helper II

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
Not applicable

Yep!

Helper II

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!

Community Champion

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

Proud to be a Super User!

Helper II

@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
Not applicable

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)

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors