cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TwanGoltstein
New Member

Archiving data to create trends

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.

1 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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors