HOW TO DISPLAY ONLY CHANGES (text) OVER A PERIOD

[Steph83]

Good day,

I have people who sometimes change teams. I want to display a table that resumes for a given period (one year) only team changes.

example: over a period of 3 months, there are three changes:

Here is all the data:

Date	Employee	Team
01/01/2022	Robert	A
01/02/2022	Robert	A
01/03/2022	Robert	C
01/01/2022	Mickael	A
01/02/2022	Mickael	B
01/03/2022	Mickael	B
01/01/2022	Georges	B
01/02/2022	Georges	B
01/03/2022	Georges	C

I want to see the following table:

Date	Employee	Move
01/02/2022	Mickaël	A to B
01/03/2022	Robert	A to C
01/03/2022	Georges	B to C

Is it possible with DAX?

Thanks in advance for your good support!

Stephane

[Ahmedx · Accepted solution]

see attached
https://dropmefiles.com/2fLt8

[Steph83]

@Ahmedx & @Sahir_Maharaj 

Thank you both for your prompt and perfect reply

Stephane

[Sahir_Maharaj]

Hello @Steph83,

Yes, it is possible to create a table that summarizes team changes using DAX:

Team Changes = 
ADDCOLUMNS (
   FILTER ( 'Table', 'Table'[Date] >= DATE ( 2022, 1, 1 ) && 'Table'[Date] <= DATE ( 2022, 3, 1 ) ),
   "Employee", 'Table'[Employee],
   "Move", CONCATENATEX (
      FILTER ( 'Table', 'Table'[Employee] = EARLIER ( 'Table'[Employee] ) ),
      'Table'[Team],
      " to ",
      'Table'[Team],
      DATEDIFF ( MIN ( 'Table'[Date] ), 'Table'[Date], DAY )
   )
)

Use the "Team Changes" calculated table to display the summary table you want.

You can add a table visual to your report and drag the "Date", "Employee", and "Move" columns from the "Team Changes" calculated table into the table visual. This will display the summary table you want.

Let me know if you might need further help.