Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only

  • 1.  Rules and Latest Non Zero Time Period

    Posted 2 days ago
    Edited by Jeremy Aude 2 days ago

    Hello,

    I can't edit the subject but it should instead read "rules and latest completed time period" because in fact the latest period could be a zero value!

    I imagine there have been many others with the same situation but I can't find a solution. Maybe my search terms are incorrect!

    I am trying to use rules to determine an average balance in a period, which is calculated as: ( balance at beginning of period + balance a

    t end of period ) / 2. For instance:

    • Q1 2025 average balance = ( balance 1 Jan + balance 31 Jan ) / 2
    • 2024 average balance = ( balance 1 Jan 2024 + balance 31 Dec 2024 ) / 2
    • Mar 2025 average balance = ( balance 1 Mar + balance 31 Mar )/ 2

    The rules I wrote to get the beginning and ending balance are like this:

    ['Beginning Inventory Balance'] = 
        IF( ELLEV( 'Period', !Period ) > 0, 
          DB( 'Inventory', !Organization, ATTRS( 'Period', !Period, 'First Period' ), !Item, 'Beginning Inventory Balance' ), 
      CONTINUE );

    ['Ending Inventory Balance'] = 
        IF( ELLEV( 'Period', !Period ) > 0, 
          DB( 'Inventory', !Organization, ATTRS( 'Peri

    od', !Period, 'Last Period' ), !Item, 'Ending Inventory Balance' ), 
      CONTINUE );

    As you can see, this is very straightforward when I am calculating for a month or a completed period such as prior quarter or year because I am leveraging the first period and last period attributes in my date dimension. 

    I am running into issues when trying to calculate for current quarter and year. As of today, these would calculate as:

    • Q3 2025 average balance = ( balance 1 July + balance 31 July ) / 2
    • 2025 average balance = ( balance 1 Jan + balance 31 July ) / 2

    I can't just use the first and last period attributes here because 2025 element has a last period of Dec 2025 and Q3 2025 is Oct 2025 and those values do not exist yet. Do I need to add another attribute, something like "last completed period" where 2025 and Q3 2025 would have Jul 2025 value? 

    Can you help me understand how you've solved this problem in your environment?

    I appreciate any help!


    ------------------------------
    Jeremy Aude
    ------------------------------



  • 2.  RE: Rules and Latest Non Zero Time Period

    Posted 2 days ago

    Hello,

    You can create new attribute to point which month to use for each quarter. This should applies to the year element.

    Cheers.



    ------------------------------
    Def Marshal
    ------------------------------

    Original Message


  • 3.  RE: Rules and Latest Non Zero Time Period

    Posted 2 days ago

    Hello Def,

    Thanks for the quick response. This is what I was thinking, so your confirmation boosted my confidence! :-) 

    Have a great week.



    ------------------------------
    Jeremy Aude
    ------------------------------

    Original Message


  • 4.  RE: Rules and Latest Non Zero Time Period

    Posted 3 hours ago

    I have a 'system_control' cube in all my models where I keep various pieces of information used throughout the model.  Part of this is various date elements for things like CurrentFY, Current Period, Current Date, Budget Start Period and Current Reporting Period (would be similar to your last completed period).  There is a process that runs every night to check the calendar date and writes most of these elements automatically.  I make the assumption that the last calendar month is the Current Reporting Period.

    You can then use these elements in various rules and process'.

    Good Luck..



    ------------------------------
    Craig Sawers
    ------------------------------

    Original Message


Related Content