Hello, I'm a beginner in PowerBI and i got asked to try and create a couple reports. I'm not really sure what would be the correct terminology for what I'm trying to do so didn't have much luck when googling for something similar.
So the scenario is:
There's an existing application where all "access requests" go through and are stored in a database.
There are 3 types of requests (Grant access/modify access/revoke access).
The db looks like this (has lots of other stuff but they're irrelevant)
| UserID | SystemID | RequestType | RequestCompletionDate |
| John | System 1 | Grant access | 01/01/2022 |
| John | System 1 | Revoke access | 01/01/2023 |
| John | System 1 | Grant access | 01/01/2024 |
Based on this info we can deduce that John had access on System 1 between 01/01/2022 and 31/12/2022, then didn't have access from 01/01/2023 until 31/12/2023 since his access was revoked on 01/01/2023, and now he currently has access as the latest request granted him access on 01/01/2024.
So the requirements are to create 3 pages:
1)A page where you can select a system from a slicer and see all users who currently have access on that system (my approach would be somehow to find all users who got a request for that system and where the latest RequestType is not "Revoke").
2)Pretty much same thing as #1 but for a specific date instead for today, for example see who had access on "System 1" on 05/05/2023.
3)Again same thing but for a time period, as in who had access on "System 1" at any point in 2023.
What I ended up doing was create a table, use the Group by wizard as shown below and keep only the rows where the type is not "Revoke" which covers requirement #1 but no idea how to approach the other 2.
