Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a data set such as below:
I don't get the luxury of any date in the export from the 'booking' system, which isn't ideal. The export is csv.
I get an export, currenty, weekly. But this will hopefully improve to daily soon.
I need to report on the 'run rate' - on a weekly basis, how many products have been booked in the last week. Visually, this is relatively straightforward, because i can see how many rows have been added to include a 'Booked' status compared to the last export.
However, to my knowledge PowerBI can't store historical data, so when i get a new export and refresh the data, I don't know the best way to show how many new Bookings have been made since the last load....
If i went from the above dataset to the below on the next refresh:
How can I show that my run rate for the last week has been 1?
In addition, how do I also show run rate as a trend over time.....? e.g. this week has been 1, last week run rate was 2, 4 weekly avg run rate has been 2, 8 weekly average run rate.... etc... etc.
Many thanks in advance.
Solved! Go to Solution.
Hi @Nickodemus,
How big are the CSV files? Could you keep all of them and import them each time? This way you could inject a Date column via Power Query to treat these as separete tables in the data model. Definitely ugly but would at least give you the ability to compare datasets.
Agree with Phil.
If your files are too large, you can use an R-script to create a transaction-table, by comparing the current version with the previous version and writing the differences into a separate csv-file (in append-mode).
That way you would only need to import 3 files with each update:
- the current one
- the previous one
- the transactions
How the script should look like: http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/
How to apply it directly without visual: http://zomalex.co.uk/using-r-in-power-bi-part-2-execute-a-r-step-in-a-query/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
PBI can store historical data. It must be set up in a 2 step process.
Your import/refresh does indeed over-write - make that your staging/import table.
Then using Query Editor set up an Append to your Master Table.
Although each record does not have a transaction date - as Phil suggests - you want to add a unique Import Date field using the current date to your import table also using Query Editor - and be sure that it is appended to the Master. Each record of the group will have this same date - each group imported will be a different date and in this way you have the historical groups of records.
Hi @Nickodemus,
How big are the CSV files? Could you keep all of them and import them each time? This way you could inject a Date column via Power Query to treat these as separete tables in the data model. Definitely ugly but would at least give you the ability to compare datasets.
Agree with Phil.
If your files are too large, you can use an R-script to create a transaction-table, by comparing the current version with the previous version and writing the differences into a separate csv-file (in append-mode).
That way you would only need to import 3 files with each update:
- the current one
- the previous one
- the transactions
How the script should look like: http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/
How to apply it directly without visual: http://zomalex.co.uk/using-r-in-power-bi-part-2-execute-a-r-step-in-a-query/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |