Hi,
I have 4 SharePoint lists representing: 1- Security Risks, 2- related actions, 3- Related controls, and 4- Risk rating (over time).
Over time new risks, actions, and controls are created and closed.
Those lists are reviewed every 6 months.
Now there are metrics in relation to them that I want to represent on the dashboard, like how many risks, actions, or controls are open or closed or how many were closed or mitigated in a certain period.
Now risks, actions, and control have 2 date columns: 1 for when they were opened and one for when they were closed, The dates represent a period of six months (so 2021 Half1, 2021 H2, 2022 H1, 2022 H2, 2023 H1 etc etc..). If there's no 'completion date', the risk/action is still open
My problem is that I want to create a filter that would allow me to update all the visuals as they were in the time range selected on the filter.
So if I select 2022 Half 1, the 'open risk' visual should for instance show how many risks were open during that period (so should count risks that were opened during 2022 H1 or earlier and were not yet mitigated).
And the same should apply to other visuals. Does anybody know would be the best way to achieve that?
So basically the filter should allow rolling back the 'state' of SharePoint lists to the time of the selected time range
