Archiving data to create trends

[TwanGoltstein]

Hi all,

Within our organization we want to create weekly reports in which we show the current state of system status but also archived the data to create trends.

For example the dataset looks like the following on 01-07-2022 (week A):

Order	Created on	System status
1042425	05-05-2022	Approved
1055232	08-05-2022	Rejected
2042913	15-05-2022	In progress
1085424	28-05-2022	Approved
1020536	02-06-2022	In progress
1950293	07-05-2022	Rejected

We want to know how many orders have a system status of Approved at this moment.

In this case it is 2.

A week later (week B) the dataset has changed to the following (08-07-2022):

Order	Created on	System status
1042425	05-05-2022	In progress
1055232	08-05-2022	Approved
2042913	15-05-2022	In progress
1085424	28-05-2022	Rejected
1020536	02-06-2022	Approved
1950293	07-05-2022	Approved
1490423	07-07-2022	Approved
4194723	05-07-2022	Rejected

We see that more orders have been added to the dataset and that the system status has changed for several orders.

We still want to know how many orders have a system status of Approved at this moment. 

Which in this case 4. 

As mentioned earlier: within our organization we want to create weekly reports in which we show the current state of system status but also archived the data to create trends.

So, how many orders had the system status "Approved" in week A and have the system status "Approved" in week B.

The current data has overwritten the old data, because the status of particular orders have been changed. 

Is it possible in Power BI to create a table like the following:

Week	System status	Amount
Week A	Approved	2
Week B	Approved	4
Week C	Approved	...

Which creates every week a new row with the current value of the amount of orders that have the status "Approved". 

Thus Power BI looks at the current state, calculates the amount, and puts it in a new table under row A.

Same goes for week B, look at the current state and put the value in a new row B.

And so on. 

So we have historical data and current data (live data).

 We receive our data through a gateway (so, it comes from a direct query).

Thank you in advance.

[BA_Pete]

Hi @TwanGoltstein ,

The closest you will get to native data warehousing with Power Query/Power BI is incremental refresh:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

This comes with its own significant caveats, such as never being able to download your accumulated data, but it may be just what your looking for in your scenario.

Beyond that, for any production-level reporting, you will need to set up an actual data warehouse to collect and store this historical information.

Pete

[TwanGoltstein]

Hi @BA_Pete,

Thank you for your reply, however, I have tried incremental refresh. But it didn't give me the result I wanted. Maybe I haven't set up the incremental refresh correctly.

Twan

[BA_Pete · Accepted solution]

Possibly. It's a bit of a tricky process.

Unfortunately, this is the only native data retention support directly connected to PowerBI.

You could look into pushing your daily data into a small SQL server using Power Automate, or even by using Power Query/R, something like this:

Ruth @ Curbal https://www.youtube.com/watch?v=ANIZkTZO3eU 

Other than that, it's a proper data warehouse and ETL process you need to go for.

Pete