Retrieving YTD value when timescale spans multiple years

Question: Is there a way to retrieve a year-to-date value for the applicable year when our module spans more than 1 reportig year? If we use the "Year To Date Summary" checkbox on the setting tab under time, it only provides the YTD value for the current year and current month selections made on the settings tab.

Accepted answers

rodney_piepho

Ok, so to start with, you will need to add a line item that just cumulates the line item you are looking to get the summary for - the formula would just be CUMULATE(Line Item). For purposes of demonstration - I named the one in my example "Cumulation Line Item" - you will see how this applies in the next section, and it should make more sense also.
Then you will have an additional line item that will return the value you are looking for - the formula will be the below:

Cumulation Line Item - OFFSET(Cumulation Line Item, -MONTH(START()), 0)

Screenshot of example module attached

This solution sponsored by Vicky Ascensio, Ryan Takao, and Ryan Whalen - thanks guys!

rodney_piepho

Hello,

I have come up with a solution that works, although it may not be the most efficient way to do it. You would still have the same setup as above, however, the only difference would be your formula for the YTD Value cell would be as follows:

IF MONTH(START()) = 1 THEN Cumulation Line item - OFFSET(Cumulation Line item, -1, 0) ELSE IF MONTH(START()) = 4 THEN Cumulation Line item - OFFSET(Cumulation Line item, -2, 0) ELSE IF MONTH(START()) = 7 THEN Cumulation Line item - OFFSET(Cumulation Line item, -3, 0) ELSE Cumulation Line item - OFFSET(Cumulation Line item, -4, 0)

I have also provided a screenshot below for context.

simon_ritchie

Hi,

Here's another approach that uses an IF based on the period start() date vs the years start date.
See screenshots.

Thanks,
Simon

All comments

rodney_piepho

PrevContributor

HI

Can you please suggest a similar solution for the case where timescale used is Quarter and not month

rodney_piepho

simon_ritchie

Hi,

Here's another approach that uses an IF based on the period start() date vs the years start date.
See screenshots.

Thanks,
Simon

PrevContributor

Seems it could another simple variant for this case
I made 2 calcs with no CUMULATE and OFFSET functions

YTD - with reset of cumulation in the beginning of each year
QTD - with reset of cumulation in the beginning of each quarter
Hope this helps too

PrevContributor

Thanks a lot for your help. However in the meanwhile I also figured away out, but your idea is helpful too. I will post my solution in a bit, its similar to what Rodney has suggested here.

Quick Links

Can you help?

Unanswered questions

ANAPLAN XL View
Hi, I need help in pulling a specific view in Anaplan XL. The first grid should display attributes of various projects, while the second grid should present the monthly financial data for these projects. Thanks in advance.
Add list members to a selective access list
Hi, I am working on a solution where the User should only have access to write/edit the list members that they add from an Action on the frontend. This action adds another list member to a selective access list. The problem is, in order to run the action, you need access to the entire list, so you would see all list…
Dimensionality Issue
Hi ALL, In my model, I have Need stored at a 'D3' RS Group level. I need to use this Need in another module at the G5 Location level. I currently have a mapping module where each G5 Location maps to a D3 RS Group. This Module uses D2 Role as dimension. but the line item I've to find the RS Group (Find D3 RS Group = 'FIND…