Formula for comparison of previous quarters and months

[PrevContributor]

I am looking for a correct formula where, in a destination module, if I do a time selection on a specific month I get the periodic value and the ytd value of that month and the previous month. But in the same line item the formula should hold when I select a quarter. So when selecting a quarter it should give me the periodic value and the ytd value of that quarter and the previous quarter.   Currently the set up is the following:  

SOURCE MODULE	TIME
Account	Line Item	jan	feb	mar	Q1	apr	may	june	Q2
Sales	Periodic	2	3	4	9	5	6	7	18
Sales	YTD	2	5	9	9	14	20	27	27

DESTINATION MODULE	Line item
Account	Time	Periodic Actual	Periodic Previous	YTD Actual	YTD Previous
Sales	June	7	6	27	20
Sales	Q2	18	15	27	20

[img height="81" width="491"]file:///C:\Users\mverbist\AppData\Local\Temp\msohtmlclip1\01\clip_image002.png[/img]  

Formulas:	Periodic Actual	Source Module.Periodic
	Periodic Previous	OFFSET(Source Module.Periodic , -1 , 0)
		LAG(Source Module.Periodic , 1 , 0)
	YTD Actual	Source Module.YTD
	YTD Previous	OFFSET(Source Module.YTD , -1 , 0)
		LAG(Source Module.YTD , 1 , 0)

    The lag and the offset formulas refer to 1 period earlier on the timescale and they have the same behaviour. It works fine if you select a month but as you can see it does not give the correct values when selecting a quarter. The values indicated in red are wrong, they should both be 9.   So could anyone provide me with a correct formula? Or some solution how I can make this work?    Thanks!   Mathias

[peter_mcanena]

Hi Mathias, Your formulae are all fine; the problem is your summary methods. If you change the Time Summary for YTD Actual and YTD Previous to "Closing Balance" then you'll get the results you want. To do this, go to the blueprint and click the ellipsis in the Summary entry for the YTD Actual line item. The main Summary is probably already to "Sum", which is fine; just change the Time Summary to "Closing Balance". Then do the same for YTD Previous. Hope this helps, Pete

[PrevContributor]

Hi Peter, thanks for your repsons, unfortunately your answer is not correct. I already have the summary methods the way you describe them so that will not solve the problem. The problem is that if I select a quarter, the lag or the offset formula only looks 1 period back instead of 3. so somehow I should be able to differentiate between a quarter and a month in the formula but I have no knowlegd of such a formula. 

[peter_mcanena · Accepted solution]

OK I see. In that case you need some intermediate line items:

• Periodic Previous Month
• Periodic Previous Quarter
• YTD Previous Month
• YTD Previous Quarter

In the Previous Month line items you put the formulae you've currently got in Previous (i.e. LAG(Source Module.Periodic , 1 , 0) for Periodic Previous Month), and in the Previous Quarter line items you put the same formulae but lagging by three months instead of one (i.e. LAG(Source Module.Periodic , 3 , 0) for Periodic Previous Quarter). Then in Periodic Previous and YTD Previous you can decide which of the two to pick up by saying

IF MONTH(START()) = MONTH(END()) THEN Periodic Previous Month ELSE Periodic Previous Quarter

and

IF MONTH(START()) = MONTH(END()) THEN YTD Previous Month ELSE YTD Previous Quarter

respectively. You can now change the summary methods of these line items to Formula, and Anaplan will pick up either the previous month or the previous quarter as appropriate. I've attached the full blueprint in case this isn't clear. I've got it working using the attached.

[PrevContributor]

Works perfectly, thanks a lot! Mathias