Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
BKnecht
Helper II
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!

 

QTD_Error.png

 

 

 

1 ACCEPTED SOLUTION
v-caliao-msft
Microsoft Employee
Microsoft 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)
Capture.PNG

The create the QTD column use the DAX below.
QTD = CALCULATE(SUM(FiscalQTD[Sales]),FILTER(ALLEXCEPT(FiscalQTD,FiscalQTD[FYQuarter]),FiscalQTD[Date]<=EARLIER(FiscalQTD[Date])))
Capture1.PNG

 

The report looks like below.
Capture2.PNG

 

Regards,

Charlie Liao

View solution in original post

11 REPLIES 11
v-caliao-msft
Microsoft Employee
Microsoft 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)
Capture.PNG

The create the QTD column use the DAX below.
QTD = CALCULATE(SUM(FiscalQTD[Sales]),FILTER(ALLEXCEPT(FiscalQTD,FiscalQTD[FYQuarter]),FiscalQTD[Date]<=EARLIER(FiscalQTD[Date])))
Capture1.PNG

 

The report looks like below.
Capture2.PNG

 

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!

CahabaData
Memorable Member
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.

www.CahabaData.com
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).

Worked Perfectly...Thanks!

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!

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
Community Champion
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.