cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Steph83
Frequent Visitor

HOW TO DISPLAY ONLY CHANGES (text) OVER A PERIOD

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:

DateEmployeeTeam
01/01/2022RobertA
01/02/2022RobertA
01/03/2022RobertC
01/01/2022MickaelA
01/02/2022MickaelB
01/03/2022MickaelB
01/01/2022GeorgesB
01/02/2022GeorgesB
01/03/2022GeorgesC

 

I want to see the following table:

DateEmployeeMove
01/02/2022MickaëlA to B
01/03/2022RobertA to C
01/03/2022GeorgesB to C

 

Is it possible with DAX?

 

Thanks in advance for your good support!

Stephane

1 ACCEPTED SOLUTION
Ahmedx
Solution Sage
Solution Sage
3 REPLIES 3
Ahmedx
Solution Sage
Solution Sage

@Ahmedx & @Sahir_Maharaj 

Thank you both for your prompt and perfect reply

Stephane

Sahir_Maharaj
Community Champion
Community Champion

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.


Did I answer your question? Mark my post as a solution, this will help others!  

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
P.S. Want me to build your Power BI solution?
Data Scientist | Data Engineer | Data Analyst | AI Engineer

➤ Website: https://sahirmaharaj.com

➤ Email: sahir@sahirmaharaj.com

➤ Lets connect on LinkedIn: Join my network of 12K+ professionals

➤ Want me to build your Power BI solution? Lets chat about how I can assist!

➤ Join my Medium community of 30k readers! Sharing my knowledge about data science and artificial intelligence


Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors