I have a monthly report run from Oracle HCM. I want to compare one month's data to another, but not to duplicate each time.
In effect - month 2 compared to month 1; mth 3 - mth 2; mth 4 - mth 3 etc.
The tables I have so far...
The BASE DATA > ROLE NAME is what I'm primarily looking at - to see what Oracle HR User Roles each individual have. This could be from 1 to maybe 20 roles per person - and I need to extract and highlight those people who have changed one or more of their roles from the previous month. I've hit a dead-end and can't see a way passed - relatively new to PBi
Thank you Neeko!
1. In any way possible - but preferably a table of just those that have changed from one month to the next.
2. As it's an audit tracker of user's roles, I need to know who, what, when a role has changed. There's 15 columns and over 20,000 rows in each monthly report.
3. Yes. All users will by default have EMPLOYEE, they could have any number of other roles specific to their job.
I'm unable to share any of the data due to Org's security.
| User Name | Person Number | Full Name | Assignment Status Type | Job Name | Role Name | Role Added Date | Role End Date | Group |
| A@work.com | 000001 | Smith, Alan | Active | Employee | Org Employee | 01/01/2020 | South West | |
| A@work.com | 000001 | Smith, Alan | Inactive | HR Systems | Org HR Sys | 03/03/2019 | 02/02/2021 | South West |
| A@work.com | 000001 | Smith, Alan | Active | HOBC | Org HOBC | 04/04/2020 | South West | |
| Z@work.com | 000002 | Jones, Bob | Inactive | HRAM | Org HRAM | 01/05/2019 | 10/12/2021 | North East |
| Z@work.com | 000002 | Jones, Bob | Active | ABM | Org ABM | 01/05/2019 | North East | |
| Z@work.com | 000002 | Jones, Bob | Active | Employee | Org Employee | 01/05/2019 | North East |
If the above was month 1, and month 2 showed any details in any of the last 6 columns as being different, then it would pull the complete row into a new table say "Monthly Changes".
I hope this makes sense!
Hi @Aj72 ,
I have several questions about your description:
1. In what format would you like to highlight the comparison results?
2. If a user has multiple roles in a month, is it different to compare all the roles of this month to the next?
3. Is it possible for a user to have different roles at the same time period?
Please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
