Presenting progress over time from a single data set

[timcorke]

Good morning experts,

I'm looking for a bit of guidance, I've got a single data set of c. 50,000 rows that contains a snapshot of IDs, their start time, wait time, their area of focus etc that is updated twice weekly by our BI team. Within this data set I calculate their respective breach months from the start date at both 78 and 104 weeks which allows me to show that if not resolved, how many IDs per breach month there could be. I'm trying to visualise the progress of the teams looking after these IDs in reducing the number of IDs that are meeting these breach timeframes.

At each update, various IDs that have been resolved disappear, (because this is a snapshot of two dates in the week) preventing me from seeing a Start and End date for all IDs and using various calculations to see totals of IDs completed per month, per area, avg, time of completion etc.

I don't really want to increase the file size of the doc by adding each updates as it's already quite large, but cannot see how I can record progress week on week without doing so. Other files within our org use VBA macros to record a snapshot of key metrics from the data against each particular week but am wondering whether there's a way within PowerBI to do this as I'm less familiar with VBA?

Thanks in advance.

[timcorke]

Thanks @v-kkf-msft - sorry for the delay coming back to you. This is a tricky area but think that I've managed to find a way to do what we needed by referencing a folder which contains the same formatted files that capture updates over time.

[v-kkf-msft · Accepted solution]

Hi @timcorke ,

Your data is updated twice a week, and each update removes historical data. You want to calculate the number of IDs per month without keeping the historical data, am I understanding you correctly?

Unfortunately, currently Power BI can only do calculations on data imported into the model, not on deleted historical data.

If you just want to export reports periodically, you can use Rest API (Reports - Export To File) and Power Automate to achieve that. If you want to do calculations on historical data, maybe try incremental refresh.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.