Forum Discussion
Historical data - Source overwritten overnight
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 :
- I put the payment date as a filter, but if a customer pays by instalment, it doesn't work.
- 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!!
2 Replies
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.
Indeed, I would need an historical of each column headers colored in red.
But how to do it, that's what I'm unsure.
