Dynamic Month to Month Variance

Hello Friends,

I need your help. I have attached a file. Column A have Customer's and then months from Dec'22 till Dec23.

Column O is for M-M Variance. It is Month to Month Variance.

Currently have data till May month. Now in Column O (M-M Variance) formula is May - April, Once we will be in the month of July then we will have amount for June then, we have to check the Variance of June - May.

So the tool should be Dynamically work every month and in future years.

Can someone help me on this?

Book3.xlsx

Data Investigation

Accepted answers

FinnCharlton

Hi @ArijitRoy , this workflow should do the trick. It dynamically finds the most recent two months that have non-zero values, and then calculates the MoM variance from those, before appending it onto the original dataset. Hope it helps!

Dynamic Month on Month variance.yxmd

All comments

FinnCharlton

Dynamic Month on Month variance.yxmd

ArijitRoy

Hi @FinnCharlton,

Thanks for the help. I am facing a new issue. If there is a new customer in the month of May and there will be no data in April then MoM variance is coming as null. It is not keeping the value of May.

FinnCharlton

Hi @ArijitRoy , in this case I recommend replacing the nulls with zeros, check out the attached workflow

Dynamic Month on Month variance.yxmd

ArijitRoy

Hi @FinnCharlton,

Kindly check the attached picture. I have also attached a new input file. According to the new file and Picture, if there are no data in previous months then MoM - Variance is null. It should keep the value of the May month. This tool is not giving the proper result.

Pic.png

Book3.xlsx

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335