Determine if a value was present in previous month

Jocky — Mon, 19 Dec 2022 17:40:14 GMT

Hi there,

I have data that is structured somewhat as follows:

Employee number	Month
111	2022 01
222	2022 01
333	2022 01
444	2022 01
555	2022 01
666	2022 01
777	2022 01
888	2022 01
999	2022 01
111	2022 02
233	2022 02
333	2022 02
444	2022 02
455	2022 02
555	2022 02
666	2022 02
777	2022 02
888	2022 02
111	2022 03
233	2022 03
333	2022 03
444	2022 03
455	2022 03
555	2022 03
777	2022 03
888	2022 03
899	2022 03

As you can see, the data is a continuous set of employee numbers for multiple periods of time (Jan to March 2022). In January, all employees are present but as the months go on, so new ones are added (eg 233, 455 and 899) and some are deleted (eg 222, 999 and 666).

I am looking for a solution that will determine what values are in a current month that are already there in the previous month. So for example, 111 is there in Jan, Feb and Mar, therefore it is a yes for Feb and Mar. Whereas 233 is only there in Feb and Mar, so it is a yes for Mar but no for Feb (as it wasn't there in January.

Example of output.

Employee number	Month	There previous month?
111	2022 01
222	2022 01
333	2022 01
444	2022 01
555	2022 01
666	2022 01
777	2022 01
888	2022 01
999	2022 01
111	2022 02	Y
233	2022 02	N
333	2022 02	Y
444	2022 02	Y
455	2022 02	N
555	2022 02	Y
666	2022 02	Y
777	2022 02	Y
888	2022 02	Y
111	2022 03	Y
233	2022 03	Y
333	2022 03	Y
444	2022 03	Y
455	2022 03	Y
555	2022 03	Y
777	2022 03	Y
888	2022 03	Y
899	2022 03	N

Re: Determine if a value was present in previous month

amitchandak — Mon, 19 Dec 2022 18:13:11 GMT

Jocky , Try like

New column
var _cnt = countx(filter(Table, [Employee Number] = earlier([Employee number) && [Month] < earlier([Month])), [Employee Number])
return
if(isblank(_cnt), "N", "Y")

for Measure approch refer

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

Re: Determine if a value was present in previous month

CNENFRNL — Mon, 19 Dec 2022 19:57:29 GMT

Flag = NOT ISEMPTY( FILTER( HR, HR[Employee number] = EARLIER( HR[Employee number] ) && HR[Month] < EARLIER( HR[Month] ) ) )

topic Re: Determine if a value was present in previous month in DAX Commands and Tips

Determine if a value was present in previous month

Re: Determine if a value was present in previous month

Re: Determine if a value was present in previous month