Joiners and leavers based on salary

[Gangasaniravi]

Hello,

want to calculate joiners and leavers based on salary , please find sample data.

Id	Month-Year	Salary	Headcount
83	Jan-21	100	1
83	Feb-21	200	1
83	Mar-21	300	1
83	Apr-21	400	1
83	May-21	500	1
83	Jun-21	600	1
83	Jul-21	700	1
83	Aug-21		1
83	Sep-21		1
83	Oct-21		1
83	Nov-21		1
83	Dec-21		1
83	Jan-22		1
83	Feb-22		1
83	Mar-22		1
83	Apr-22		1
83	May-22		1
83	Jun-22		1
84	Jan-21		1
84	Feb-21		1
84	Mar-21		1
84	Apr-21		1
84	May-21		1
84	Jun-21		1
84	Jul-21		1
84	Aug-21	250	1
84	Sep-21	340	1
84	Oct-21	500	1
84	Nov-21	700	1
84	Dec-21		1
84	Jan-22		1
84	Feb-22		1
84	Mar-22		1
84	Apr-22		1
84	May-22		1
84	Jun-22		1
85	Jan-21		1
85	Feb-21		1
85	Mar-21		1
85	Apr-21		1
85	May-21		1
85	Jun-21		1
85	Jul-21		1
85	Aug-21	800	1
85	Sep-21	200	1
85	Oct-21	200	1
85	Nov-21	450	1
85	Dec-21		1
85	Jan-22		1
85	Feb-22		1
85	Mar-22		1
85	Apr-22		1
85	May-22		1
85	Jun-22		1
86	Jan-21		1
86	Feb-21		1
86	Mar-21		1
86	Apr-21		1
86	May-21		1
86	Jun-21		1
86	Jul-21		1
86	Aug-21		1
86	Sep-21		1
86	Oct-21		1
86	Nov-21		1
86	Dec-21	100	1
86	Jan-22	200	1
86	Feb-22	300	1
86	Mar-22	400	1
86	Apr-22	500	1
86	May-22	600	1
86	Jun-22	700	1
87	Jan-21		1
87	Feb-21		1
87	Mar-21		1
87	Apr-21	800	1
87	May-21	200	1
87	Jun-21	200	1
87	Jul-21		1
87	Aug-21		1
87	Sep-21		1
87	Oct-21		1
87	Nov-21		1
87	Dec-21		1
87	Jan-22		1
87	Feb-22		1
87	Mar-22		1
87	Apr-22		1
87	May-22		1
87	Jun-22		1
88	Jan-21		1
88	Feb-21		1
88	Mar-21	300	1
88	Apr-21	400	1
88	May-21	500	1
88	Jun-21	600	1
88	Jul-21	700	1
88	Aug-21		1
88	Sep-21		1
88	Oct-21		1
88	Nov-21		1
88	Dec-21		1
88	Jan-22		1
88	Feb-22		1
88	Mar-22		1
88	Apr-22		1
88	May-22		1
88	Jun-22		1

 Logic for joiners: 

IF(AND([Current_Month]<>0,[Previous_Month]=0),sum([Headcount]))

Logic for Leavers: 

  IF(AND([Previous_Month]<>0,[Current_Month]=0),sum([Headcount]))

we need to use headcount from data not to hardcode it as 1, Not able to get data for leavers.

output:

Month-Year	Headcount	salary	Joiners	Leaver
01 January 2021	1	100	0	0
01 February 2021	1	200	0	0
01 March 2021	2	600	1	0
01 April 2021	3	1600	1	0
01 May 2021	3	1200	0	0
01 June 2021	3	1400	0	0
01 July 2021	2	1400	0	1
01 August 2021	2	1050	0	0
01 September 2021	2	540	0	0
01 October 2021	2	700	0	0
01 November 2021	2	1150	0	0
01 December 2021	1	100	0	1
01 January 2022	1	200	0	0
01 February 2022	1	300	0	0
01 March 2022	1	400	0	0
01 April 2022	1	500	0	0
01 May 2022	1	600	0	0
01 June 2022	1	700	0	0

[Gangasaniravi]

Thanks @ryan_mayu for looking into this i am using direct query and have relation between fact table and date dimension based on date column. above solution is correct but not working with DQ

[ryan_mayu]

i seldom use DQ mode, let's see if anyone else can help you

[ryan_mayu]

@Gangasaniravi 

the first row is for 2021 Jan and the salary is 100, why the joiners for that month is 0?

[Gangasaniravi]

@ryan_mayu fir Jan 2021 headcount is 1 , there is no prrior moth to compare to start

joiners and lever is 0

[ryan_mayu]

@Gangasaniravi 

pls see the attachment below 

the output is sightly different from you expected.

e.g. 2021 aug should have 2 joiners, but what you expected is 0

pls see the attachment below