Refresh with new excel daily file and store existing data - HELP PLEASE!

[JCBI2023]

Hi All, 

I have searched here and couldnt find an answer to my exact solution. I am also new in the building game so help would be very much appreciated. 

I have a daily excel file that I use and overwrite the previous days (the first table in screenshot below), refresh the report and the report updates. I am also creating another manual excel that contains some of the summed historic data - i use this data to create a few different graphs. The bottom table is the one I want to create automatically when I overwrite and refresh yesterdays report. 

any help is much appreciated 

[johnyip]

@JCBI2023 If you need to automate the manual Excel descibed in your "below table", you may utilize VBA or marco scripts to do the aggregrations of your daily new data and then generate a record in the table.

Otherwise, in my knowledge, you could only update the table manually.

Moreoever, I would recommend you adding a date column in your "above table" so the old data can be retained.

By doing so, you don't have to maintain the "below table".

But you will have to make adjsutments to the DAX of your measures/calculated columns by implementing this changes, since the new structure includes data of all times. From your words your report is reading all the data without scanning the date. You need to modify the DAX in the way that it responds to the slicer that you will put in your reprot

[JCBI2023]

Thanks for the quick response 🙂 

From what I read online here: https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...

and other sources, it looks as though you can create a table inside powerBI that inputs this data when a refresh happens and stores it. when you refresh with new data, it appends the table and so on. 

I only have powerBI and excel to work with so hoping its possible to do this so I can stop doing a manual sheet every day. 

[johnyip · Accepted solution]

your attached link is about incremental refresh on PowerBI service.

From your sample data, you utilize the manual table that stores the aggregrated values to build your visualizations.

Incremental refresh cannot help in your scenario, unless you add a date column in your "above table".

But by doing so, again you will need to make adjustments to your report and the DAX.

[JCBI2023]

here is the data as well: 

Daily file contains this data at client level
ADV NAME	ADV CODE	ACCOUNTS	ACCOUNTS FUNDED	FLOWS
a	1	129	117	$        37,862,111
b	2	15	14	$        15,548,998
c	3	14	12	$        15,689,027
d	4	61	44	$        15,551,788
e	5	56	42	$        12,850,326
f	6	43	26	$        12,049,452
manual file I make
Date	Total accounts	total funded accounts	Flows
9/11/2023	318	255	$                 109,551,701
8/11/2023	300	245	$                 100,000,000
7/11/2023	295	240	$                    95,000,000