Retrieving YTD value when timescale spans multiple years

Options
rodney_piepho
rodney_piepho Member, ALL USERS, Employee, Certified Model Builder Posts: 5 Master Anaplanner of the Year
in Planning & Modeling

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.

Tagged:

Comments

  • rodney_piepho
    rodney_piepho Member, ALL USERS, Employee, Certified Model Builder Posts: 5 Master Anaplanner of the Year
    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
    rodney_piepho Member, ALL USERS, Employee, Certified Model Builder Posts: 5 Master Anaplanner of the Year
    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.

Categories