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):
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):
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:
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.
Go to 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.
Proud to be a Datanaut!
View solution in original post
Hi @TwanGoltstein ,
The closest you will get to native data warehousing with Power Query/Power BI is incremental refresh:
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.
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.
Click here to learn more about the September 2022 updates!
Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!