Hi Guys,
can I ask a real silly question:
I have a table A with table columns follow:
Customer entry no. | trans no. | type transaction | value dates
and another table B with table columns following:
Customer entry no. | Given date | balance value
There is a relationship between tabel A Customer entry no. and table B customer entry no. as many to one.
Now how can I have measure based on date, to dynamically work out balance value of table B for each Customer entry no. by sum the transaction values of table A?
In excel, it is really easy, just use sumifs, something like =SUMIFS(A[transaction value],A[Customer entry no],B[Customer entry no],A[dates],"<="&B[Given date]), but in power BI, I am stuck, can someone please help?
Tables and datas as below: Table A
Customer entry no. trans no. type transaction value dates
1 1 Initial entry 21 23/06/2018
1 2 application -10 01/05/2018
1 3 application -14 15/09/2018
1 4 application -2 05/12/2018
2 5 Initial entry 3 06/02/2019
2 6 application 12 07/06/2019
2 7 application -14 10/08/2018
2 8 application 11 16/02/2019
3 9 Initial entry -2 25/06/2018
3 10 application -8 01/02/2018
3 11 application -14 15/05/2019
3 12 application 12 01/06/2018
4 13 Initial entry -7 06/07/2019
4 14 application -6 13/09/2019
4 15 application 20 24/11/2018
4 16 application -4 02/07/2019
5 17 Initial entry 24 11/09/2019
5 18 application 5 24/02/2018
5 19 application 13 25/01/2018
5 20 application 19 16/04/2018
Table B:
Customer entry no. Given date balance value
1 12/01/2019 -5
2 12/01/2019 -14
3 12/01/2019 2
4 12/01/2019 20
5 12/01/2019 37
6 12/01/2019 53
7 12/01/2019 50
8 12/01/2019 21
9 12/01/2019 13
Thanks very much in advance guys
