Artificially created line with dates and values

[angel0v89]

Hi all, 

I have read a lot of the discussions here and I am really struggling to find a solution for the following case: 

I have the following dataset: 

Period_Start_Date	Contract_Number	Invoice_Number	Invoice_Date	Amount 1	Amount 2	Amount 3	Amount 4
2021-02-01	1234	5678	2021-01-18	77777	222	10	222
2021-03-01	1234	5678	2021-01-18	77777	11	20	4
2021-04-01	1234	5678	2021-01-18	77777	222	40	5
2021-05-01	7777	8888	2021-03-15	12512	444	50	1
2021-06-01	7777	8888	2021-03-15	12512	555	60	6

Each Contract has it's contract number and invoice number, 1 contract may have more than 1 invoice number. 

I would like my dataset to always start from the invoice_date date, as such I need to create an extra line at the top of each contract / invoice number so I can have the following: 

Period_Start_Date	Contract_Number	Invoice_Number	Invoice_Date	Amount 1	Amount 2	Amount 3	Amount 4
2021-01-18	1234	5678	2021-01-18	77777	0	0	77777
2021-02-01	1234	5678	2021-01-18	77777	222	10	222
2021-03-01	1234	5678	2021-01-18	77777	11	20	4
2021-04-01	1234	5678	2021-01-18	77777	222	40	5
2021-03-15	7777	8888	2021-03-15	12512	0	0	12512
2021-05-01	7777	8888	2021-03-15	12512	444	50	1
2021-06-01	7777	8888	2021-03-15	12512	555	60	6

I will add some colors for clarity: 

Period_Start_Date = First Invoice Date  (marked in yellow)

Contract_Number = Same as the first line of the contract/invoice  (marked in orange) 

Invoice_Number = Same as the first line of the contract/invoice  (marked in orange)

Invoice_Date = Same as the first line of the contract/invoice  (marked in yellow)

Amount 1 = Same as the first line of the contract/invoice (marked in blue)

Amount 2 = Always 0 for the first line (the created line) 

Amount 3 = Always 0 for the first line (the created line)

Amount 4 = Amount 1 

Can anyone suggest anything here?

[OllieClarke]

Hey @angel0v89 

How's this:

Using the group by feature in the sample tool means we can focus on the first row per invoice number

edit: updated the formula tool to fix the period date

[angel0v89]

Hi there,

This works very well!, thank you very much! However, it seems that I have got some outliers, where the date is in the middle of the invoice period. For such cases, I would like to add all values in Amount 2 to Amount 3 before this date. Example: 

Period_Start_Date	Contract_Number	Invoice_Number	Invoice_Date	Amount 1	Amount 2	Amount 3
01/04/2021	79973	5678	23/09/2021	7,129	39.61
01/05/2021	79973	5678	23/09/2021	7,129	594.09
01/06/2021	79973	5678	23/09/2021	7,129	594.09
01/07/2021	79973	5678	23/09/2021	7,129	594.09
01/08/2021	79973	5678	23/09/2021	7,129	594.09
01/09/2021	79973	5678	23/09/2021	7,129	594.09
23/09/2021	79973	5678	23/09/2021	7,129	0	3010.06	Artificial record
01/10/2021	79973	5678	23/09/2021	7,129	594.09	594.09
01/11/2021	79973	5678	23/09/2021	7,129	594.09	594.09
01/12/2021	79973	5678	23/09/2021	7,129	594.09	594.09
01/01/2022	79973	5678	23/09/2021	7,129	594.09	594.09
01/02/2022	79973	5678	23/09/2021	7,129	594.09	594.09
01/03/2022	79973	5678	23/09/2021	7,129	594.09	594.09
01/04/2022	79973	5678	23/09/2021	7,129	554.4	554.4

Any suggestion or help? Obviously, I can't do a group by because I will break all other records... somehow I need to find the match and then sum the previous period and discard the other records.

[OllieClarke]

Hey @angel0v89 

Does this work:

I wasn't sure how the Amount 1 value was supposed to work for those middle records. so I took the last one out of the summarise.