So I'm working with import mode in power, connected to an Azure SQL server,
the financial data I'm working with have 2 fields as estimations and real income
the problem is that when estimation became a real figure.
an example to better understand
order number 100:
client ask me to make a transportation for some goods and I receive this order and based on my estimation will estimate revenue of 50 for this order.
When this transport will be done, I will be invoiced to the client (usually different from estimation) let's say 40
problem is that at this moment of invoicing, the database will cancel the estimation and keep just the invoicing amount.
so, for example, at the end of January ( from 1 Jan to 31 Jan) I will have 100 of estimation and 50 of invoicing,
( because of some transportation still in progress)
if I will check for the same period after some months, I will see the figure from January 1 to 31, as 0 for the estimated amount, and 90 as the invoiced amount ( because I will send the actual invoice and system will cancel the estimation and take care just about invoicing)
so I need to keep records for example in a new table of the records of 1 of every month so I can see how change figures with time.
