Forum Discussion
Daily Amount
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 ...
SachinNandanwar
Impactful Individual
Impactful IndividualCan you provide this data in text format ?
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
Super User
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)returndate(_year,_month,_day)2. Create a date table from 2015 to 2025Date = 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.
