Hello everyone,
first of all the note that I am not 100% sure if this is the right place to ask my question. But I am optimistic and hope that it fits here.
I am currently creating a report for our demand management tool, which was custom built based on Dynamics 365. We capture all current business requirements in this tool and maintain details and status there. Now I'm in the process of converting the current manually updated PowerPoint charts into interactive and up-to-date Power Bi dashboards. And now we come to my problem, because in the PowerPoints it is so that there are many charts that show a historical comparison. For example, there is a chart that shows how many requests with the status "In Progress" changed to the status "Finalized", in the last 4 weeks. However, the table that includes the content that is maintained in Dynamics has no way to show what has changed, how, and when. So I would need a table that in the end contains a weekly/monthly copy of all requests, over e.g. the whole last year, to which i can compare my current status.
My requirements are to implement this problem with Microsoft's own tools and not to set up SQL/NoSQL databases, since I don't get any resources for this.
Current data situation:
- Access to the current data from Dynamics via the Dataverse
- about 170 entries (+ 30 - 40 every year) with about 200 columns
- There is a unique ID for each entry which can be referred to
My current thoughts are as follows:
1. implement the whole thing with Power Query and somehow create a table where all new entries are copied in with the date of their synchronization. Then I would filter the data so that only the last year is displayed, perhaps to limit the amount of entries.
2. build a routine via Power Automate, which saves the current table every week and populates it with a date. I have only no idea how I get the data then automated in Power Bi and also link it.
I know that's not really a common question, but I'm stumped at the moment and could use some new input. With a bit of luck, there might be someone here who has had to solve this problem before.
Thanks for reading and your participation!
