Running total by group and by date

[Anonymous]

Hello,

I have the following table and i am trying to create a running total column for the Invoice column for each Document ID by Received date. The date format is: dd/mm/yy

From my understanding, the first step would be to sort the Document ID column and then the Received date column should be sorted in order starting from oldest date but it should keep the Invoice amount according to the date on the same row. Then, the running total should be calculated separately for each Document ID. I'm not sure if it's better to do this in Power Query or DAX but any help is much appreciated!

Document ID	Received	Invoice
3998	01/07/2022	398
3998	07/04/2022	178
3998	06/02/2022	451
4060	05/01/2022	646
4139	05/05/2022	657
4145	06/07/2022	443
4149	05/06/2022	155
4150	05/08/2022	408
4150	10/07/2022	704
4151	05/09/2022	411
4152	05/10/2022	403
4166	15/07/2022	472
4168	25/07/2022	729
4169	05/08/2022	670
4169	02/01/2022	548
4187	09/04/2022	414
4248	22/02/2022	185
4248	05/03/2022	110
4248	06/03/2022	88
4248	15/04/2022	250
4249	21/01/2022	644
4250	11/03/2022	1546
4481	25/02/2022	583
4487	15/01/2022	661
4495	07/07/2022	1419
4685	26/02/2022	5446
4686	17/03/2022	545
4687	25/06/2022	654
4688	01/02/2022	1398
4688	02/03/2022	2574
4688	17/05/2022	3384
4931	09/05/2022	658
4997	18/08/2022	713
4997	19/06/2022	421
5139	05/05/2022	7487
5232	16/04/2022	646
5325	22/04/2022	451
5418	29/05/2022	613
5512	29/07/2022	751
5605	28/08/2022	184

Expected results (partial):

Document ID	Received	Invoice	Running Total
3998	06/02/2022	451	451
3998	07/04/2022	178	629
3998	01/07/2022	398	1027
4060	05/01/2022	646	646
4139	05/05/2022	657	657
4145	06/07/2022	443	443
4149	05/06/2022	155	155
4150	10/07/2022	704	704
4150	05/08/2022	408	1112
4151	05/09/2022	411	819

[HotChilli]

Good stuff.  I pointed you in the right direction and you got it done.

[HotChilli]

There is a Running Total Quick Measure for this.  Also the Expected result for Invoice 4150 doesn't look right

[Anonymous]

@HotChilli Thanks for your reply! I will investigate the Running Total Quick Measure but it should be limited per Document ID. I have fixed the expected results.

[Anonymous · Accepted solution]

@HotChilli I have created the following quick measure:

The corresponding auto-generated DAX code:

Invoice running total in Received = 
CALCULATE(
	SUM('Invoice Table'[Invoice]),
	FILTER(
		ALLSELECTED('Invoice Table'[Received]),
		ISONORAFTER('Invoice Table'[Received], MAX('Invoice Table'[Received]), DESC)
	)
)

The results in a table is correct:

[rends_04]

Hi I used this fine on just dates, but as soon as I put the year into a pivot table the calculation begins from fresh each year in the rolling count of the buy quantity. Any ideas how to alter the formula to correct?

ie 27/01/2021 should be 29, 13 from 1st year and 16 from the transaction on the 17/01/2021