Best approach for having daily updated data that compares with previous 21 days

[FoolzRailer]

Hello

I'm looking for the best approach to automating a task I'm trying to do. I was thinking of using Power BI for this, but if your suggestions are to use something else (because Power BI is not a viable solution) feel free to give me your feedback.

I'm looking to create a price and stock comparision visualisation. Each day I recieve an update file for a lot of companies price and stock for their products. I'm trying to see what has been sold the last 21 days, basically I need to do a rolling compare between files (might be better done in Access?). Then I would like to compare the prices and stock between these companies based on the products.

The files I will recieve are mostly .txt, .csv or .xlsx, each time the file schematics should be the same.

The easy bit is the comparing of the files, I can just build relationships based in EAN numbers, but the watching the stock change the last 21 days is stumping me.

Is Power BI the correct tool for this, or should I do some Access/Excel before Power BI.

Sample data for one file, on two different days:

Number	Type	Price	Stock	Producer	Modelname	EAN nummer
17116	Hardware	144	5	Corsair	CMSO4GX3M1A1333C9	843591007146
22244	Hardware	494	1	Apple	MD564ZM/A	885909575794
27802	Hardware	82	14	Logitech	980-000513	5099206028074

Number	Type	Price	Stock	Producer	Modelname	EAN nummer
17116	Hardware	140	1	Corsair	CMSO4GX3M1A1333C9	843591007146
22244	Hardware	500	40	Apple	MD564ZM/A	885909575794
27802	Hardware	82	10	Logitech	980-000513	5099206028074

[JLaine]

Disclamer:  I am a power BI novice myself, so I may not know all the details or have the best soltuion, but...

You will need a date column in your source data.  Then you could build a measure of price difference of the data filtered by most recent date vs the same data filtered by 21 days ago (use the 'calculate' and 'filter' DAX functions)

So, I think this woudl be done via 3 overall steps:

1] Import your source data files into one large table where the file date is one of the columns.

2] Defilne the measure mentioned above.

3] Use a table visual where the colums are the most recent date, the part, the current price, and the "21-day price difference" measure.

If the measure is defined correctly, this should remain current each time you update your source data.

See also this video which shows the process for a year over year comparison:  https://www.youtube.com/watch?v=JNt-_QByeLk

[FoolzRailer]

@JLaine Appreciate the reply. I forgot to mention that these are pretty large pricefiles (large .txt files in general). If I load everything, 21 days of each companies file, into Power BI and grab the respective files Created By date to use as a column for calculating the Stock, won't I get a really huge/slow dataset?

The files are anywhere between 1-35 mb for each company, multiplied by 21 days of data. 

Appreciate the help!

[JLaine]

@FoolzRailer I suggest just trying importing all files' data (only the columns you actually need), and see how big your Power BI file gets.  I think you'll be surprized by just how small it really is.

See also: https://community.powerbi.com/t5/Desktop/CSV-import-How-to-preserve-quot-Date-created-quot-column-in...

[JLaine]

@FoolzRailer Power BI stores datasets in a much more compact format than that of the original files.  I think you'll be surprised by just how much data compression occurs once the original files' overhead has been ignored.

[v-easonf-msft]

Hi, @FoolzRailer 

If possible, it is recommended to integrate the data of these 21 tables into the database in advance, and then use PowerBI to access.

Best Regards,
Community Support Team _ Eason

[FoolzRailer]

@v-easonf-msft Any suggestions for that? Would an SQL database, where I do some work first with linking IDs be the best bet?