Historical data - Source overwritten overnight

[mchacha]

Hi! 

I'm having trouble to get an historical of my source to then be able to do measure like 'vs day - 1', 'vs LY' because it gets overwritten daily. 

• I have a report that works fine which shows KPIs such as : 

Knowing that I have filters (Year, Period, Type of customers and so on) 

Campus	Number of customers	Amount invoiced	Average price	Amount paid	Amount pending	% paid
Paris	1000	10 000 000	10 000	8 500 000	1 500 000	85,00 %
Freiburg	500	500 000	1 000	450 000	50 000	90,00 %
Lausanne	50	68 000	1 360	54 400	13 600	80,00 %
Madrid	300	897 654	2 992	834 818	62 836	93,00 %
Total	1 850	11 465 654	15 352	9 839 218	1 626 436	85,81 %

=> I would like to know how much % paid we had for instance last day / month / year for the same filters. 

But also a breakdown per Payment status and how they evolve (the ones that were fully unpaid uncontrolled, do they pay in the end or do they cancel the invoice?). 

• My source gets overwritten overnight, I simplified it : 

It's an Excel file (I can't link PowerBi to my company ERP) which is 35MB and has 201k lines. This is my main source, I've linked it to other excel sources (to have more qualitative information about the customers). 

I put in red the field that I need not to be overwritten

Campus	Customer code	Amount invoiced	Paid	Pending	Payment date	Payment status	Period	Year	Type of customer	Type of invoice
Paris	11289210	10000	5000	5000	10/10/2023	Payment plan	T1	2024	New	Regular
Freiburg	11289209	1000	1000	0	03/10/2024	Paid	T1	2024	Not new	Deposit
Lausanne	16933752	1360	0	1360		Unpaid - Uncontrolled	T1	2024	New	Special
Madrid	22578295	2992,18	1496,09	1496,09	10/10/2023	Unpaid - Controlled	T1	2024	Not new	Regular

My ideas and their limits : 

1.  I put the payment date as a filter, but if a customer pays by instalment, it doesn't work. 
2. I put a node in Knime to keep each source and add the date at the end and save it in a Sharepoint folder. In Powerbi, I duplicated my fact table, one to take the latest date and another table to take latest date - 1 (to have the day before). The issue is, my PowerBI takes already around 30 min to refresh (in service), thus I'm afraid it would take too much time. 

I think I'm complicating my life with ideas 2/ and I could maybe adjust my source to have the historical in it directly... 

Do you have any ideas? 

Thank you!!

[danextian]

There's no problem calculating how much were already paid at a given date but that the payment amount as well as the payment date should be in the data historically. For every transaction a new line should be created.

[mchacha]

Indeed, I would need an historical of each column headers colored in red. 

But how to do it, that's what I'm unsure.