Previous for a monthly result only.

elaine.novel
elaine.novel
in Modeling

Hi Team, 

 

Is there a way I can use Previous but won't affect my previous or succeeding month?

Example:

Jan 1 - 1

Jan 31 - using Previous I will get 1

Feb 1 - 0 (should not get my result in Jan 31)

Feb 3 - 2

Feb 28 - using Previous I will get 2

 

The previous will only get monthly result and will reset next month?

 

Thanks in advance,

Elaine

Tagged:

Best Answer

Answers

  • tingtingxia
    tingtingxia
    Hi Elaine

    You may need to use IF ELSE statement in this case. IF the date is a start of a month, you reset the value, ELSE you use PREVIOUS value.

    Tingting
  • KirillKuznetsov
    KirillKuznetsov

    You can check out MOVINGSUM function with LASTNONBLANK aggregation argument applied.

    https://help.anaplan.com/37394929-ea62-4e55-9655-b8c8c2732679-MOVINGSUM

    But please note that LASTNONBLANK works with TEXT only. So first you will have to convert it to TEXT and then after calculation from text back again to VALUE.

    The formula should look something like this (with an additional line item to be calculated - Days from start of the month):

    VALUE(MOVINGSUM( TEXT(Line Item), Days from start of the month, 0, LASTNONBLANK))

  • elaine.novel
    elaine.novel

    Hi KirillKuznetsov, 

     

    The problem with this is not every month shares the same days for me to use movingsum. Could you please illustrate how to make this work?

     

    Thanks, 

    Elaine

Categories