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,
Go to Solution.
View solution in original post
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))
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?