Calculating Previous month Number from current month

[shivanand612]

Hello ,

I have an interesting case here. 

I have dataset which has 3 months of data and data includes Emp ID, Date, Emp Designation, State, Town, City. 

i would like to calculated the previous month # emp are there in it.

Note; if the emp got promoted then his designation will change also his location might change.

attached the data set here.

Thank you 

Data set:

Date	Emp ID	EMP Designation	State	Town	City
01-12-2024	A1234	Associate 1	IND	CA	T1
01-12-2024	B2345	Associate 1	HKG	GK	T1
01-12-2024	C3456	Associate 1	BPJ	JP	T1
01-12-2024	D4567	Associate 1	DWR	WR	T1
01-12-2024	E5678	Associate 1	SWR	WR	T1
01-12-2024	F6789	Associate 1	UBL	BL	T1
01-12-2024	G2583	Associate 2	SWE	WE	T2
01-12-2024	H3691	Associate 3	UBL	BL	T2
01-01-2025	A1234	Associate 1	IND	CA	T1
01-01-2025	B2345	Associate 1	HKG	GK	T1
01-01-2025	C3456	Associate 1	BPJ	JP	T1
01-01-2025	D4567	Associate 1	DWR	WR	T1
01-01-2025	E5678	Associate 1	SWR	WR	T1
01-01-2025	F6789	Associate 1	UBL	BL	T1
01-01-2025	G2583	Associate 2	SWE	WE	T2
01-02-2025	A1234	Associate 1	IND	CA	T1
01-02-2025	B2345	Associate 1	HKG	GK	T1
01-02-2025	C3456	Associate 1	BPJ	JP	T1
01-02-2025	D4567	Associate 1	DWR	WR	T1
01-02-2025	E5678	Associate 1	SWR	WR	T1
01-02-2025	F6789	Associate 1	UBL	BL	T1
01-02-2025	G2583	Associate 2	SWE	WE	T2
01-02-2025	H3691	Sr. Associate	IND	CA	T3
01-02-2025	K1476	Sr. Associate	SWE	WE	T3

Result :

Date	CM Month	PM Month
01-02-2025	9	7
01-01-2025	7	8
01-12-2024	8	0

[Luke_C · Accepted solution]

Hi @shivanand612 

Here's one way based on your example:

1. Format date to be a date field
2. Group and count by date
3. Sort dates
4. Use multi-row tool to get the previous month count

[lwolfie]

I would try something like this.

[lwolfie]

@Luke_C  My coffee wasn't on board yet to remember the Multi-Row tool.  That's why Alteryx is great, because there are multiple ways to do something!

[shivanand612]

Thank you Luke!

In the large data i am not getting correct numbers  if I add the city as my group in Multirow formulae i am getting near by number.

is there any way to do this calculation dynamically. 

Add - In my result i want to show all the dimension which i mentioned above 

Thank you

[shivanand612]

.

[shivanand612]

Give more context to this.

I am looking the result like this. 

Date	Emp ID	EMP Designation	State	Town	City	CM Count	PM Count
01-02-2025	A1234	Associate 1	IND	CA	T1	1	1
01-02-2025	B2345	Associate 1	HKG	GK	T1	1	1
01-02-2025	C3456	Associate 1	BPJ	JP	T1	1	1
01-02-2025	D4567	Associate 1	DWR	WR	T1	1	1
01-02-2025	E5678	Associate 1	SWR	WR	T1	1	1
01-02-2025	F6789	Associate 1	UBL	BL	T1	1	1
01-02-2025	G2583	Associate 2	SWE	WE	T2	1	1
01-02-2025	H3691	Sr. Associate	IND	CA	T3	1	0
01-02-2025	K1476	Sr. Associate	SWE	WE	T3	1	0
01-01-2025	A1234	Associate 1	IND	CA	T1	1	1
01-01-2025	B2345	Associate 1	HKG	GK	T1	1	1
01-01-2025	C3456	Associate 1	BPJ	JP	T1	1	1
01-01-2025	D4567	Associate 1	DWR	WR	T1	1	1
01-01-2025	E5678	Associate 1	SWR	WR	T1	1	1
01-01-2025	F6789	Associate 1	UBL	BL	T1	1	1
01-01-2025	G2583	Associate 2	SWE	WE	T2	1	1
01-12-2024	A1234	Associate 1	IND	CA	T1	1	0
01-12-2024	B2345	Associate 1	HKG	GK	T1	1	0
01-12-2024	C3456	Associate 1	BPJ	JP	T1	1	0
01-12-2024	D4567	Associate 1	DWR	WR	T1	1	0
01-12-2024	E5678	Associate 1	SWR	WR	T1	1	0
01-12-2024	F6789	Associate 1	UBL	BL	T1	1	0
01-12-2024	G2583	Associate 2	SWE	WE	T2	1	0
01-12-2024	H3691	Associate 3	UBL	BL	T2	1	0

Thank you

[Luke_C · Accepted solution]

Hi @shivanand612 

Added a second branch to accomplish this, it takes into account the employee ID and matches your output.

[shivanand612]

thank you Luke! Awesome! :)

[shivanand612]

Luke, 

If you summaries at month level it wont give you the PM count