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 ...
Can 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 |
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.
