Previous for a monthly result only.


Hi Team, 


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


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,


Best Answer


  • 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.

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

    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))

  • 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?