Calulate distinctcount of codes present in one list AND not in the other

[AFra]

Hi all, 

I have a list of elements for each month. Some of these elements are present in the past month AND this month, others are not. Some others change status. 

I'd like to create three measures : 

- distinctcount of NEW elements (are present this month but not the previous month)

- distinctcount of GONE elements (were present last month but aren't anymore this month)

- distinctcount of elements that changed status 

So that for this table : 

CODE	Status	Date
abc1	R1	01-02-21
abc2	R1	01-02-21
abc3	R2	01-02-21
abc4	R2	01-02-21
abc5	R3	01-02-21
abc6	VL1	01-02-21
abc7	VL1	01-02-21
abc8	VL2	01-02-21
abc1	R1	01-03-21
abc3	R1	01-03-21
abc4	R1	01-03-21
abc5	R2	01-03-21
abc6	VL1	01-03-21
abc8	VL1	01-03-21
abc9	VL2	01-03-21
abc10	VL2	01-03-21
abc11	R2	01-03-21
abc12	R2	01-03-21

I would get : 

- 4 new elements

- 2 gone elements

- 3 elements that changed status

      - 1 from R2 to R1

      - 1 from R3 to R2

      - 1 from VL2 to VL1

I started with creating a column that concatenates the code and the status. I guess I have to use the CALCULATE, DISTINCTCOUNT and MAX(Date) fonctions, but I can't figure it right. 

Anyone? Thanks a lot! 

[Anonymous · Accepted solution]

Hi @AFra ,

The results of the three measures are shown in the figure below. There should be 4 elements that change the state, they are abc3, abc4, abc5, abc8.

You can check details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AFra]

Thank you so much! It's exactly what I needed : )