YOY and MOM Dynamic

[shivanand612]

Hi All,

I wanted to calculate the YOY and MOM % change for the below data, however I have tried many ways but, not reaching the output that I wanted. Below is the sample data I have here but I have very big data set to apply this YOY and MOM% change to apply.

EMP ID	Date	Deposit	Cash	Credit	Deposit AVG	Cash AVG	Credit AVG
E2233445	1/1/2020	806	26	0	823	96	29
E2233445	1/2/2020	747	34	40	940	92	27
E2233445	1/3/2020	771	68	0	926	95	38
E2233445	1/4/2020	582	75	0	943	96	32
E2233445	1/5/2020	94	58	0	909	94	80
E2233445	1/6/2020	118	90	0	1001	99	73
E2233445	1/7/2020	141	65	0	1219	90	37
E2233445	1/8/2020	766	84	60	1291	100	45
E2233445	1/9/2020	311	33	0	1101	89	76
E2233445	1/10/2020	221	60	0	1012	95	40
E2233445	1/11/2020	810	20	0	989	96	54
E2233445	1/12/2020	208	36	50	1278	81	24
E2233445	1/1/2021	133	85	0	1020	92	69
E2233445	1/2/2021	971	93	0	949	92	42
E2233445	1/3/2021	255	63	0	1269	95	25
E2233445	1/4/2021	757	69	33	1052	94	80
E2233445	1/5/2021	102	58	23	924	98	66
E2233445	1/6/2021	540	26	44	838	88	53
E2233445	1/7/2021	216	34	7	1026	98	75
E2233445	1/8/2021	223	12	5	1253	92	34
E2233445	1/9/2021	292	40	6	976	95	22
E2233445	1/10/2021	222	75	12	1068	97	21
E2233445	1/11/2021	414	82	11	888	84	49
E2233445	1/12/2021	387	84	32	1112	83	56
D3344556	1/1/2020	600	26	0	871	86	30
D3344556	1/2/2020	747	34	40	944	92	55
D3344556	1/3/2020	771	68	0	772	121	58
D3344556	1/4/2020	345	75	0	894	111	94
D3344556	1/5/2020	94	58	0	1122	118	81
D3344556	1/6/2020	118	50	0	789	89	22
D3344556	1/7/2020	141	12	0	858	113	65
D3344556	1/8/2020	650	84	50	1018	91	75
D3344556	1/9/2020	311	33	0	967	105	98
D3344556	1/10/2020	221	60	0	792	101	96
D3344556	1/11/2020	200	20	0	1068	88	80
D3344556	1/12/2020	208	23	34	817	111	36
D3344556	1/1/2021	133	75	0	955	118	74
D3344556	1/2/2021	500	32	0	1013	97	79
D3344556	1/3/2021	255	23	0	994	99	30
D3344556	1/4/2021	443	69	3	1080	107	26
D3344556	1/5/2021	102	58	23	911	119	33
D3344556	1/6/2021	540	26	44	979	123	70
D3344556	1/7/2021	216	34	7	766	71	30
D3344556	1/8/2021	223	12	5	739	110	79
D3344556	1/9/2021	292	40	6	954	120	78
D3344556	1/10/2021	222	75	21	794	85	43
D3344556	1/11/2021	212	45	17	833	118	72
D3344556	1/12/2021	387	66	32	1009	78	36

I have calculated the YTD based on Year using the summarize tool that is sorted and i wanted the below output.

EMP ID	Date	Deposit YTD	Last Deposit AVG	YOY %	MOM %	Cash YTD	Last Cash AVG	YOY %	MOM %	Credit YTD	Last Credit AVG	YOY %	MOM %
E2233445	1/12/2021	4512	1112	86.05769	-6.52173913	721	83	133.3333	2.439024	173	56	-35.7143	190.9091
D3344556	1/12/2021	3525	1009	86.05769	82.54716981	573	78	186.9565	46.66667	149	36	-5.88235	88.23529

Below are some Notes.

For all the  AVG : this is the latest record of the particular month report. 

YOY % formulae; Previous year same month - Current year same month/ Previous year same month * 100

MOM % : Previous month - Current month /previous month * 100

Thank you

Shiva

[atcodedog05]

Hi @shivanand612 

Your YoY and MoM formula is not clear I was able to get columns other than that. Please provide more clarity.

Hope this helps : )

[shivanand612]

Hi,

Here is my formulae for YOY and MOM % Change

YOY 

EMP ID	Date	Deposit	YOY %
E2233445	1/12/2020	208	(387-208)/208*100
E2233445	1/12/2021	387	86.05769231

MOM 

EMP ID	Date	Deposit	MOM %
E2233445	1/11/2021	414	(387-414)/414*100
E2233445	1/12/2021	387	-6.52173913

I hope this helps 

Best 

Shiva

[atcodedog05]

Hi @shivanand612 

Here is the updated workflow. Avg is still not matching can you explain on that. This workflow is dynamic if years changes calculations will automatically get adjusted.

Hope this helps : )

[shivanand612]

Thank you for you reply and Answer!

below is the scenario to get the AVG Number

EMP ID	Date	Deposit AVG
E2233445	1/5/2021	924
E2233445	1/6/2021	838
E2233445	1/7/2021	1026
E2233445	1/8/2021	1253
E2233445	1/9/2021	976
E2233445	1/10/2021	1068
E2233445	1/11/2021	888
E2233445	1/12/2021	1112

In the above data the AVG number is 1112, if I wanted to Calculate the % of Deposit then I need to Use the last number for the year (1112).

All the AVG numbers are like that...

Regards,

Shiva 

[atcodedog05 · Accepted solution]

Hi @shivanand612 

Awesome, it matches. Please find the updated workflow.

Hope this helps : )

[shivanand612]

Thank You atcodedog05 :)

[atcodedog05]

Happy to help : ) @shivanand612 

Cheers and have a nice day!