How to determine updated records on refresh

[Nickodemus]

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.

[Phil_Seamark · Accepted 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.

[CahabaData]

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.

[Phil_Seamark · Accepted 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.

[ImkeF · Accepted solution]

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/