Previous for a monthly result only.

elaine.novel
Contributor

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?

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

1 ACCEPTED SOLUTION

Accepted Solutions
KirillKuznetsov
Certified Master Anaplanner

Re: Previous for a monthly result only.

4 REPLIES 4
tingtingxia
Contributor

Re: Previous for a monthly result only.

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
Certified Master Anaplanner

Re: Previous for a monthly result only.

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
Contributor

Re: Previous for a monthly result only.

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

KirillKuznetsov
Certified Master Anaplanner

Re: Previous for a monthly result only.