I have a project that started February 1, 2023. Each day my dataset receives a refresh of all data from the start date through yesterday.
The data needs to be separated by one column of data: "Assigned to". The data is then separated and presented by the 3 categories from the "Assigned to" column: "New Referrals" "Imaging" and "Follow Up".
The data points I need to show are as follows:
1.) Yesterday's Totals
2.) Overall Totals
3.) Timeline showing data from each individual day starting with the start date of the project through yesterday
Sounds easy enough until I throw in the fact that each line of data can change daily behind the scenes.
For example: On 2/1/2023 one line of data is "Assigned to": "New Referrals". On 2/6/2023 that same line of data has now been "Assigned to": "Follow Up". I need to track this line of data from the original "Assigned to": "New Referrals" on 2/1/2023 for the above mentioned Timeline, as well as the new "Assigned to": "Follow Up" in Yesterday's Totals 2/6/2023.
The data files I use are as follows:
STATIC_New Referrals.xlxs - Original daily data from 2/1/23 to 3/12/23
New Referrals.xlsx - Daily run of all data from 2/1/23 to 3/14/23
* from this file I create "Yesterday's Totals" into it's own query as well as separate into the 3 categories needed
Timeline Issues:
My though process told me to APPEND "STATIC_New Referrals" with "Yesterday's Totals" to continually build one file with each days data to be used for the timeline. The problem I am having is "STATIC_New Referrals.xlxs" is stored on SharePoint and is refreshed in Power BI each day. In turn, this changes the data back to the original file that only runs up to 3/12/23 and then APPENDS "Yesterday's Totals" leaving me with dates from 2/1/2023 to 3/12/23 and 3/14/23 (yesterdays date). I'm left with missing dates in between (3/13/23).
How do I create a file that will truely be STATIC and continually add yesterdays data to it? I would appreciate any feedback as I am fairly new to Power BI and there may be a better way to pull the data that I am not thinking of. Thank you in advance!
Lisa
