Calculating monthly progress within overwriting data set

[Gdua]

Hi All,

New to the community and this is my first query. So, I use a data set that I have to overwrite every month for my monthly report. Typically my data set deals with 5-6 'programs' (initiatives) and numerous 'projects' under each program/initiative. As projects get completed, they drop off the data set as I only report on 'active' projects. So, the number of 'active' projects change on a monthly basis.

What I want to do is to report monthly progress (see examply below). Currently, I maintain a separate file for each program and manually record the number of active projects each month. I want to eliminate the manual data entry and automate this through a forumla in excel or power query. The problem I have is that I have to overwrite the dataset each month, which makes it difficult to use a formula.

I hope this makes sense and many thanks in advance.

[v-weiyan1-msft]

Hi @Gdua ,

I am not sure if I understood your question correctly.
Based on your description, you need to cover the dataset every month. Instead of replacing the entire dataset each month, it may be worthwhile to add new data to the existing dataset. This way, you can maintain a history of all your projects, including those that are no longer active. You can use Power Query in Power BI to merge monthly datasets into a single comprehensive dataset, or you can choose to refresh the data periodically.
Regarding your reference to "As projects get completed, they drop off the data set", as far as I know, Power BI does not support automatic data deletion currently.
For more you may refer to:

Data refresh in Power BI - Power BI | Microsoft Learn

Best Regards,
Yulia Yan

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