Rolling Quarter Formula

[#1]

Hi All,

I'm trying to do a Rolling Quarter SET Analysis. I have two scenarios I am done with one, which is the correct Quarter 1.2.3 and 4. I need help with when I have to do deal with irregular quarter such as Dec to Feb or Feb to Apr. I have created a Text Box in which I want to click on Month field and I want the Text Box to be filtered according to the DimMonth.

Thanks in Advance,

[sunny_talwar]

What exactly are you trying to accomplish? If someone selects Feb2016 you want to do something like this?

(Feb2016 - Dec2015)/Feb 2016 ??

[#3]

Yes if someone clicks on Feb 2016 in the month field 2 and dim year 2016 I want the text box under the Accounts Number to show me the formula from Dec 2015 to Feb 2016. The text box on the left has already been formatted with accordance to the proper Quarter cycles.

[#4]

The Difference of Quarter from Jan 2016 - Nov 2015 between the quarter of Oct 2015 to Aug 2015.

[sunny_talwar]

Don't you have any date field in your application?

[#6]

Yes I do it is called the Accounting Date.

[sunny_talwar]

Does this give the result you are looking for?

=(Sum({[BookRev]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(Max(accountingdate))) & '<=' & Date(MonthEnd(Max(accountingdate))))"}>} grossamount)

-

Sum({[BookRev]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -2))) & '<=' & Date(MonthEnd(AddMonths(Max(accountingdate), -2))))"}>} grossamount) * 4)

/

Sum({[BookCost]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -2))) & '<=' & Date(MonthEnd(AddMonths(Max(accountingdate), -2))))"}>} grossamount)

[#8]

Hi Sunny, thanks for all your effort thus far. I want to be able to click on a given month from the Month field , for argument sake Jan 2016 and I want to get the formula like the following.

1) I click on Jan 2016

2) The formula provides me with the total sum of (Book Rev of Jan 2016 to Nov 2015) subtracted by the (BookRev of Oct 2015 to Aug 2015) times by 4 divided by BookCost of Oct 2015 to Aug 2015.

=num((Sum({[BookRev]<QuarterNum={"$(=Max({$}QuarterNum))"}, [DimYear] ={"$(=Max({$}[DimYear]))"}>} grossamount)

-

Sum({[BookRev]<QuarterNum={"$(=If(Max({$}QuarterNum) - 1 = 0, 4, Max({$}QuarterNum)-1))"}, [DimYear] ={"$(=If(Max({$}QuarterNum) - 1 = 0, Max({$}[DimYear]) - 1, Max({$}[DimYear])))"}>} grossamount)) * 4

/

Sum({[BookCost]<QuarterNum={"$(=If(Max({$}QuarterNum) - 1 = 0, 4, Max({$}QuarterNum)-1))"}, [DimYear] ={"$(=If(Max({$}QuarterNum) - 1 = 0, Max({$}[DimYear]) - 1, Max({$}[DimYear])))"}>} grossamount),'#,###.##')

You had provided me with the above formula for the proper Quarter cycle, which works perfectly but this formula is only feasible for a proper Quarter cycle such as first, second, third and fourth quarters.

Thanks in advance,

[sunny_talwar · Accepted solution]

I am not sure if you have the number that would be calculated once we select 2016 in DimYear and Jan in DimMonth, but I am getting -2.747 using the following formula:

=(Sum({[BookRev]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -2))) & '<=' & Date(MonthEnd(Max(accountingdate))))"}>} grossamount)

-

Sum({[BookRev]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -5))) & '<=' & Date(MonthEnd(AddMonths(Max(accountingdate), -3))))"}>} grossamount) * 4)

/

Sum({[BookCost]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -5))) & '<=' & Date(MonthEnd(AddMonths(Max(accountingdate), -3))))"}>} grossamount)

Can you check if this gets you what you wanted?

[#10]

The number should get me close to 1.00 (positive). You can do choose and calculate on whatever calendar fields you want Sunny, I used Month and NumMonth because I was told by a senior Developer in order for you to have a Rolling Quarter you have to use iterno() in the scripting.

Thanks for your help.