Workaround needed to 'Lock' the Data from Previous Months

[CMoppet]

Hello

I need some creative ideas to help me 'lock' data at month end when a table is refreshed and overwritten with new data.

I provide a report with a bar chart showing MTBF data over time, with each bar representing a month.  The chart can be sliced according to the machine brand/age/market/type/territory/supplier, etc.

My MTBF calculation relies on knowing the number of repairs logged each month, and the number of active machines in the month.

The repairs part is fine... have a Work Order data table where each row represents a machine repair and includes the repair date.  Every month, I refresh it with the newest month's data added to the bottom...the table 'grows' by about 1,500 rows a month and is all fine. 

The problem I have is with the table that provides the count of active machines in the market each month.    This table has to be refreshed every month, with some machines remaining, some dropping off because they've been uplifted, and some new machines being added. 

The issue I have is that the MTBF for previous months automatically changes every month when I refresh the active machine table because the measure is reflecting the count of active machines in the latest refresh of the table, instead of holding on to whatever the count was previously.

I can't just re-run the full list of active machines every month and add it to the bottom with a transaction date, because the spreadsheet would be almost a million rows after a year.  

How can I create a new table (or another solution?) that summarises the number of active machines (according to all the filterable fields) each month, so that I don't lose this number every time I refresh?

Any ideas much appreciated as it's really blown my mind trying to solve this!!

Thanks

[audreygerred · Accepted solution]

Can you ask whoever manages your datawarehouse to create another table or view that will hold the historic data? To your point about a spreadhseet growing to a million rows, a workaround for this would be to run your file every month and save it as csv and store it in a SharePoint folder and add a new file to this folder (make sure the schema is teh same each month) every month. In Power BI you would use the SharePoint folder connector and in Power Query some of the applied steps would be to append these files together during refresh, so you don't have to worry that it is growing, each month you would just have a new file that appends with the rest.

[CMoppet]

Hello 🙂  Thanking for taking the time to reply!

There's no support with this....I maintain it all myself.  I run the data from reports I've built in SalesForce which I export in to Excel, and then refresh the data in BI after.

Based on your reply, I'll investigate the option you described...I don't know what appending is, but at least you've pointed me in a direction!  Thanks 🙂

[audreygerred]

Happy to help - here is a link with instructions 🙂

Power Query SharePoint folder connector - Power Query | Microsoft Learn