Custom Quarter periods used in calculations

[steph_io]

I have created a Date Calendar table with my custom fiscal and quarter periods (running from September 1):

"FYearQuarter", "FY " & IF(MONTH([Date])>=9,YEAR([Date])+1,YEAR([Date])) & "/Q" & CEILING(IF(MONTH([Date])>=8,(MONTH([Date])-8)/3,(MONTH([Date])+4)/3),1)

Now how do I use them in my calculations for QTD and Previous Quarter (for QoverQ growth):

AccountsQTD = CALCULATE([Count Accounts],(DATESQTD('Date'[Date])))

AccountsPrvQtr = CALCULATE( [Count Accounts], DATEADD('Date'[Date], -1, QUARTER))

AccountsQOQGrowth = ([Count Accounts] - [AccountsPrvQtr])/[AccountsPrvQtr]

The reference to functions: QTD and QUATER no longer applies.

Thanks for your help.

[v-huizhn-msft]

Hi @steph_io,

Have your issue been resolved? If you have not, please feel free to ask. If you have, please mark the corresponding replay as answer, or welcome to share your own solution. More people can fins the workaround quickly and easily. 

Best Regards,
Angelia

[steph_io]

The selection of the 'current quarter' works:

CurrentFQuarter = LOOKUPVALUE('Date'[FYearQuarter],'Date'[Date],TODAY())

It looks up the value of TODAY's date in my date table and provides the FiscalYearQuarter.

To get Fiscal Quarter over Quarter growth %:

AccountsFPrvQtr = CALCULATE( [Count Accounts], DATEADD('Date'[Date], -1, QUARTER))

AccountsFQOQGrowth = ([Count Accounts] - [AccountsFPrvQtr])/[AccountsFPrvQtr]

This works if I place it in a table visual with the fiscal quarter to group the counts:

"FYearQuarter", "FY " & IF(MONTH([Date])>=9,YEAR([Date])+1,YEAR([Date])) & "/Q" & CEILING(IF(MONTH([Date])>8,(MONTH([Date])-8)/3,(MONTH([Date])+4)/3),1)

FYearQuarter	Count Accounts	AccountsFPrvQtr	AccountsFQOQGrowth
FY 2016/Q4	770	920	-16%
FY 2017/Q1	840	770	9%
FY 2017/Q2	820	840	-2%
FY 2017/Q3	170	500	-66%

But the QuarterToDate is really acting like a count, and not accumulating quarters:

AccountsFQTD = CALCULATE(COUNT(CRMAccounts[E-mail (Primary Contact)]), FILTER('Date','Date'[FYearQuarter] <= 'Date'[CurrentFQuarter]))

FYearQuarter	YearMonthnumber	Count Accounts	AccountsFQTD
FY 2016/Q4	201606	300	300
FY 2016/Q4	201607	290	290
FY 2016/Q4	201608	180	180
FY 2017/Q1	201609	260	260
FY 2017/Q1	201610	260	260
FY 2017/Q1	201611	320	320
FY 2017/Q2	201612	230	230
FY 2017/Q2	201701	270	270
FY 2017/Q2	201702	320	320
FY 2017/Q3	201703	170	170

It should be doing this:

YearQuarter	YearMonthnumber	Count Accounts	AccountsQTD
2016/Q2	201606	300	880
2016/Q3	201607	290	290
2016/Q3	201608	180	470
2016/Q3	201609	260	730
2016/Q4	201610	260	260
2016/Q4	201611	320	580
2016/Q4	201612	230	810
2017/Q1	201701	270	270
2017/Q1	201702	320	590
2017/Q1	201703	170	760

[v-huizhn-msft]

Hi @steph_io,

Please try the fomrula below.

AccountsFQTD = CALCULATE(COUNT(CRMAccounts[E-mail (Primary Contact)]), FILTER(ALLEXCEPT('Date','Date'[FYearQuarter] ), 'Date'[YearMonthnumber]<= 'Date'[YearMonthnumber]))

Best Regards,
Angelia

[Greg_Deckler]

You would need to use a DAX variable and CALCULATETABLE function with that variable formula and use that in place of the DATESQTD. I would replace your DATEADD -1 and QUARTER with an equivalent number of days -90 DAYS for example.

[steph_io]

Can you elaborate on the DATESQTD workaround?

Using a fixed number of days would assume each month has 30 days and would not provide an adequate date range.

[Greg_Deckler]

DATESQTD returns a table of dates that you are using as a filter. The dates returned are for the quarter to the current date. You just need to simulate this using CALCULATETABLE, something along the lines of:

QuarterToDate = 

var CurrentDate = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))
var CurrentQuarter = LOOKUPVALUE( 'Date'[Quarter], 'Date'[Date], CurrentDate)

CALCULATETABLE('Dates'[Date], 'Dates'[Quarter]=CurrentQuarter  
     , 'Date'[Date]<=CurrentDate)

Something like that.

If you need to be specific in your other case to the exact number of days in the quarter, you should be able to do a similar type of calculation:

DaysInQuarter = 

var CurrentDate = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))
var CurrentQuarter = LOOKUPVALUE( 'Date'[Quarter], 'Date'[Date], CurrentDate)

CALCULATE(COUNTROWS(Date),FILTER(Date,'Date'[Quarter]=CurrentQuarter))

Something like that.