I have a pretty simple demo model created where a number of tasks have:
Task name
Category
Finish date
Project managers can take snapshots of their data which will put a copy of all the tasks under a new snapshot. The snapshot has a date.
What I would like to do now is calculate the slack. The is the difference in time between finish dates of the tasks & their respective snapshot tasks (joining both name & category). Furthermore, you will see that sometimes snapshots have been taken multiple times a month. Only the last snapshot of every month should be taken into account (perhaps this is best done through power query).
The goal is to have have simply chart which can show per Snapshot date (monthly), per snapshot task name & task category the slack (difference between finish dates) vs the task.
I kinda solved this myself, but not very satisfied with the solution.
I added the power query grouping so you only get the last snapshot per month into the model (that’s probably best practice instead virtual tables).
Then I created a few temporary keys for snapshots tasks & tasks so I can leverage those to lookup the values in tasks and calculate the slack for each snapshot task.
=> it’s the last part I would have thought to leverage the relationships which are there based on the relationships & dimensions to calculate the slack. Temporary keys feel like a dirty solution.
So if anyone has a better approach. All ears.
It’s great to know that you are making progress with your query @Wlknsn. Were you able to sort the solution you need? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
