Forum Discussion
Summing up files extracted from SharePoint folder
Super UserAnonymous so if I understood correctly all the excel files are appended together with the file name as source column, correct? So what is not working? What you are trying to achieve?
parry2k - Yes, that is correct.
The goal is to compare beginning of the month forecast, with end of the month forecast, and see what changed. And do this for each month, over the year.
- parry2k
Anonymous so file name tells which is for beginning of the month and which is the last date of the month or there is a date column that identifies start and end of the month
parry2k I do not have a start or end date column (although that was suggested in the tutorial I got the idea from).
https://spr.com/power-bi-quick-tips-dealing-with-snapshot-data/
To use my examples, the 1-6-20 is the date that someone extracted data from our CRM system (i.e. the beginning of the month).
The 1-27-20 data was the last time they ran the same report at the end of January. The goal is to compare what changed between those two files (relative to only the month of January...because each of those files actually has forecast data for the entire year...but I'm only interested in what each forecast snapshot says relative to the month the report was ran. Unfortunately we don't have a good automated way to do this.
Sorry, one more thing! There is a date column (tied to the date table) that is called Est.Close Estimated Close represents the date the salesperson thinks the deal will ship. (no idea why we called it that...but that is what it means).
So, we are comparing all of the opportunities at the beginning of the month, with the opportunities on the report at the end of the month, based on estimated close dates relative to that month.
- parry2k
Anonymous This is what I would recommend, slap sample data of few months in excel sheet (few rows) and also expected result and share it through one drive/google drive. Remove any sensitive information before sharing it.
You solved one part to append all files together, after that it is a matter of putting DAX measures together and for that need to see the data.