Hello everyone!
I have a Gsheet database with weekly snapshots of company's pipeline. All snapshots have the same columns. When a new snapshot is taken, it is added at the bottom of the file. There is a column that will tell us the snapshot date, so that we can identify every snapshot.
I built a data model in Power BI by using the aforementioned Gsheet as a source. I made two copies of the table: in one (Copy 1) I filter for last week's snapshot only, on the other (Copy 2) I filter for current week's snapshot only. I made another copy of the source table (Copy 3), and kept only unique values of opportunity IDs from last week's and current week's snapshots.
The next step was to merge Copy 3 and Copy 1, to bring in all columns from Copy 1, matched by opportunity ID (let's call it Copy 4). Then, I took Copy 4 and merged it with Copy 2. The end result (Copy 5) gives me two columns for every value in the original source: one is last week's value, and the other is current week's value. I use this to create new calculated columns and measures that allow me to determine all the movements in pipeline from one week to another.
I am happy with the results, but this is a weekly exercise. I need to be able to change the current week and last week values every time I add a new snapshot to the original source.
I tried adding the new week's values to the original source and refreshing, but Copy 1 and Copy 2 do not let me change the snapshot date filter, because the new week's data doesn't show up. Even though the original source has the data, it will not show up in either copy.
If I cannot change the filters on Copy 1, Copy 2 or Copy 3 to bring in the latest data, I am forced to rebuild the model and all the measures every week. This defeats the purpose of Power BI. I am tempted to continue using Gsheets!
Can someone offer a different way of building the model that would allow me to refresh the original data source, and to be able to update last week's and current week's tables by just changing the snapshot date filter?
Thank you!
