Daily Amount

[alperkeskin90]

Hi,

 I have account number, account suffix, amount, name and trandate. I try to make a calculation. customer made his first transaction at 30/09/2015 which positive number and this means it is a investing money. There is also negative numbers which has - before it means taking money.  I try to calculate customer's daily amount. In this example customer invest money at 30/09/2015. after this draw money. after that again draw money. I want to calculate end of day balance. Additionally, as you see the customer did not make any transaction at 03/10/2015. In this case last balance amount must be written to 03/10/2015 day balance. To sum up, I want to calculate daily balance whether the customers make transactions or not on calendar date. The date can be start beginning of the 2015 and can end on end of the 2025. If the calendar day before customer's first transaction day it can be null. I can not find any solution for this.

PS: The data has 5 million rows so i can face with memory problems.

[SachinNandanwar]

Can you provide this data in text format ?

[alperkeskin90]

please find it below. Thank you

AccountNumber	Sum of AccountSuffix	Sum of Amount	CustomerName	TranDate
abc1245	1	118,4210526	Alper	30.09.2015 00:00
abc1245	1	-112,5921053	Alper	01.10.2015 00:00
abc1245	1	-5,828947368	Alper	02.11.2015 00:00
abc1245	4	6,389473684	Alper	04.11.2015 00:00
abc1245	1	-6,389473684	Alper	01.12.2015 00:00
abc1245	3	12,17039474	Alper	03.12.2015 00:00
abc1245	1	-6,557894737	Alper	30.12.2015 00:00
abc1245	1	65,78947368	Alper	05.01.2016 00:00
abc1245	1	131,5789474	Alper	06.01.2016 00:00
abc1245	2	-193,1638158	Alper	12.01.2016 00:00
abc1245	3	-9,210526316	Alper	17.02.2016 00:00
abc1245	7	-0,341315789	Alper	23.02.2016 00:00
abc1245	1	2,894736842	Alper	25.02.2016 00:00
abc1245	2	-2,647368421	Alper	26.02.2016 00:00
abc1245	1	0,657894737	Alper	03.03.2016 00:00
abc1245	2	-1,170526316	Alper	17.03.2016 00:00
abc1245	3	0	Alper	22.03.2016 00:00
abc1245	5	0	Alper	11.04.2016 00:00
abc1245	5	0	Alper	14.04.2016 00:00
abc1245	3	0	Alper	19.04.2016 00:00
abc1245	7	0	Alper	20.04.2016 00:00
abc1245	5	0,972894737	Alper	21.04.2016 00:00
abc1245	1	7,894736842	Alper	27.04.2016 00:00
abc1245	7	-5,778684211	Alper	02.05.2016 00:00
abc1245	1	-3,088947368	Alper	01.06.2016 00:00
abc1245	9	0	Alper	06.06.2016 00:00
abc1245	7	0	Alper	07.06.2016 00:00
abc1245	4	2,718421053	Alper	13.06.2016 00:00
abc1245	1	-2,718421053	Alper	01.07.2016 00:00
abc1245	1	171,0526316	Alper	07.07.2016 00:00
abc1245	1	131,5789474	Alper	08.07.2016 00:00
abc1245	10	-301,2509211	Alper	12.07.2016 00:00
abc1245	4	-1,380657895	Alper	01.08.2016 00:00
abc1245	9	0,727894737	Alper	03.08.2016 00:00
abc1245	1	-0,727894737	Alper	01.09.2016 00:00
abc1245	3	0	Alper	06.09.2016 00:00
abc1245	3	0	Alper	07.09.2016 00:00
abc1245	11	1,051052632	Alper	08.09.2016 00:00
abc1245	1	-1,051052632	Alper	04.10.2016 00:00
abc1245	9	0,923289474	Alper	07.10.2016 00:00
abc1245	2	-0,923289474	Alper	26.10.2016 00:00
abc1245	5	0	Alper	07.11.2016 00:00
abc1245	10	2,877105263	Alper	08.11.2016 00:00
abc1245	1	-1,315789474	Alper	10.11.2016 00:00
abc1245	1	0,547631579	Alper	11.11.2016 00:00
abc1245	2	-2,108947368	Alper	21.11.2016 00:00
abc1245	21	0	Alper	05.12.2016 00:00
abc1245	2	0	Alper	06.12.2016 00:00
abc1245	1	5,486842105	Alper	16.12.2016 00:00
abc1245	1	-5,486842105	Alper	23.12.2016 00:00
abc1245	22	5,344342105	Alper	05.01.2017 00:00
abc1245	2	-5,344342105	Alper	09.01.2017 00:00
abc1245	20	0	Alper	07.02.2017 00:00
abc1245	4	0	Alper	08.02.2017 00:00
abc1245	12	0	Alper	01.03.2017 00:00
abc1245	2	0	Alper	14.03.2017 00:00
abc1245	22	0,004736842	Alper	05.04.2017 00:00
abc1245	5	10,47671053	Alper	07.04.2017 00:00
abc1245	1	-0,197368421	Alper	11.04.2017 00:00
abc1245	1	131,5789474	Alper	28.04.2017 00:00
abc1245	14	-128,5439474	Alper	02.05.2017 00:00
abc1245	7	-0,968947368	Alper	10.05.2017 00:00
abc1245	1	151,3157895	Alper	30.05.2017 00:00
abc1245	10	-145,7215789	Alper	01.06.2017 00:00
abc1245	1	-0,526315789	Alper	07.06.2017 00:00
abc1245	13	0,857105263	Alper	03.07.2017 00:00
abc1245	13	0,857105263	Alper	01.08.2017 00:00
abc1245	3	-11,66289474	Alper	15.08.2017 00:00
abc1245	1	-7,469342105	Alper	01.09.2017 00:00
abc1245	19	0	Alper	04.09.2017 00:00
abc1245	7	8,162368421	Alper	05.09.2017 00:00
abc1245	1	-8,162368421	Alper	02.10.2017 00:00
abc1245	15	0	Alper	04.10.2017 00:00
abc1245	10	0	Alper	05.10.2017 00:00
abc1245	3	0	Alper	06.10.2017 00:00
abc1245	7	0	Alper	09.10.2017 00:00
abc1245	2	49,84065789	Alper	10.10.2017 00:00
abc1245	1	-3,697368421	Alper	19.10.2017 00:00
abc1245	8	-46,14328947	Alper	01.11.2017 00:00
abc1245	9	0	Alper	06.11.2017 00:00
abc1245	8	14,08381579	Alper	07.11.2017 00:00
abc1245	1	78,94736842	Alper	30.11.2017 00:00
abc1245	4	-93,03118421	Alper	01.12.2017 00:00
abc1245	17	24,08513158	Alper	05.12.2017 00:00
abc1245	5	-24,08513158	Alper	02.01.2018 00:00
abc1245	12	0,697105263	Alper	08.01.2018 00:00
abc1245	1	-0,697105263	Alper	01.02.2018 00:00
abc1245	11	0	Alper	05.02.2018 00:00
abc1245	2	0,004210526	Alper	08.02.2018 00:00
abc1245	5	0	Alper	05.03.2018 00:00
abc1245	7	-0,004210526	Alper	04.04.2018 00:00
abc1245	5	0	Alper	05.04.2018 00:00
abc1245	7	0,656052632	Alper	06.04.2018 00:00
abc1245	5	0,101842105	Alper	02.05.2018 00:00
abc1245	1	-0,757894737	Alper	01.06.2018 00:00
abc1245	4	0,859736842	Alper	04.06.2018 00:00
abc1245	4	-0,859736842	Alper	02.07.2018 00:00
abc1245	5	0,948026316	Alper	03.07.2018 00:00
abc1245	1	-0,948026316	Alper	01.08.2018 00:00
abc1245	7	1,426710526	Alper	06.08.2018 00:00
abc1245	5	0,1025	Alper	03.09.2018 00:00
abc1245	3	1,045789474	Alper	01.10.2018 00:00
abc1245	1	15,78947368	Alper	30.10.2018 00:00
abc1245	1	-16,05947368	Alper	01.11.2018 00:00
abc1245	1	-2,305	Alper	21.06.2024 00:00

[Rupak_bi]

Hi @alperkeskin90 
Here Is your Solution.

Steps to do:

1. Forst of all your "TranDate " is not in date format. So I did an extra step to create a calculated date column in the dataset.

The formula Is as below:

New Tran date =

var A = find(".",'Table'[TranDate],1)

var _day = left('Table'[TranDate],A-1)

Var B = FIND(".",'Table'[TranDate],A+1)

Var _month = mid('Table'[TranDate],A+1,B-A-1)

Var _year = mid('Table'[TranDate],B+1,4)

return

date(_year,_month,_day)

2. Create a date table from 2015 to 2025

Date = CALENDAR(date(2015,1,1),date(2025,12,31))

3. Now Create a Measure to get cumulative sum of the amount for each customer. (Hope you have other customers as well.)

Closing balance = calculate(sum('Table'[Sum of Amount]),ALLEXCEPT('Table','Table'[CustomerName]),'Table'[New Tran date]<=max('Date'[Date]))

4. Now plot it in a Matrix using date from the date table in the row and the measure as value. Thats IT.

hop this will work. If not, let me know.