Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a table with the following data: Account, year, month, euros. Each record has the total of money that has entered or exited an account in that month. Something like that:
Account Year Month Euros
5200 2017 1 800
5200 2017 2 -100
5200 2017 3 500
5200 2017 4 - 1800
524 2017 1 100
524 2017 2 -300
524 2017 3 -200
524 2017 4 500
I want to add a column that also tells me the balance. That is ... the balance to January 2017, is the sum of all fields "euros" from the beginning to the registration that has month 1, year 2017, for a certain account. In February, it is the same sum adding the record that has month 2, year 2017. Something like this:
Account Year Month Euros Balance
5200 2017 1 800 800
5200 2017 2 -100 700
5200 2017 3 500 1200
5200 2017 4 - 1800 -600
524 2017 1 100 100
524 2017 2 -300 -200
524 2017 3 -200 -400
524 2017 4 500 100
I've tried solving it in several ways with CALCULATE, but I do not give the solution. Any ideas?
Solved! Go to Solution.
Hi @RuZaGo,
Please create a calculated column below:
Balance = CALCULATE(SUM(Table1[Euros]),FILTER('Table1','Table1'[Account]=EARLIER(Table1[Account]) && 'Table1'[Month]<=EARLIER(Table1[Month])))
Best Regards,
Qiuyun Yu
Maybe something like:
Balance = CALCULATE( SUM([Euros]), FILTER(Table, [Month] <= EARLIER([Month]))
You could also play around with the Running Total quick measure.
Hello,
I have a table with the following data: Account, year, month, euros. Each record has the total of money that has entered or exited an account in that month. Something like that:
Account Year Month Euros
5200 2017 1 800
5200 2017 2 -100
5200 2017 3 500
5200 2017 4 - 1800
524 2017 1 100
524 2017 2 -300
524 2017 3 -200
524 2017 4 500
I want to add a column that also tells me the balance. That is ... the balance to January 2017, is the sum of all fields "euros" from the beginning to the registration that has month 1, year 2017, for a certain account. In February, it is the same sum adding the record that has month 2, year 2017. Something like this:
Account Year Month Euros Balance
5200 2017 1 800 800
5200 2017 2 -100 700
5200 2017 3 500 1200
5200 2017 4 - 1800 -600
524 2017 1 100 100
524 2017 2 -300 -200
524 2017 3 -200 -400
524 2017 4 500 100
I've tried solving it in several ways with CALCULATE, but I do not find the solution. Any ideas?
Hi @RuZaGo,
Please create a calculated column below:
Balance = CALCULATE(SUM(Table1[Euros]),FILTER('Table1','Table1'[Account]=EARLIER(Table1[Account]) && 'Table1'[Month]<=EARLIER(Table1[Month])))
Best Regards,
Qiuyun Yu
It works! Thank you very much
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |