Sum of values based on filters for 2 other columns

[Mina2019]

Hi, I'm a power BI newby so I would really appreciate some help:)

So I have som loans. The table below shows the outstanding balance.

Date	Loan	Balance
01.01.2019	a	500
01.04.2019	a	400
01.07.2019	a	300
01.10.2019	a	200
01.01.2020	a	100
01.04.2020	a	0
01.03.2019	b	750
01.06.2019	b	500
01.09.2019	b	250
01.12.2019	b	0

I need a Result column that would show me the outstanding balance of all loans at the beginning. So basically I need it to add the amount on the earliest day for all loans. (OBS: Both start and end dates for loans a and b are different). 

Date	Loan	Balance	Result
01.01.2019	a	500	1250
01.04.2019	a	400	1250
01.07.2019	a	300	1250
01.10.2019	a	200	1250
01.01.2020	a	100	1250
01.04.2020	a	0	1250
01.03.2019	b	750	1250
01.06.2019	b	500	1250
01.09.2019	b	250	1250
01.12.2019	b	0	1250

Thanks in advance

[v-evelk]

Hi,

I am not sure that completely undersood your case but I recommend to perform complex calculation using DAX (also check please this article).

If you have further questions regarding building of data model for Power BI will be better adress them to DAX commands and tips thread or Desktop thread.

Kind Regards,

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

[Mina2019]

Oh, sorry for blurry explanation:)

Basically, I need Power BI to go through each loan (a and b) and determine the first date for each of them (here it will be 01.01.2019 for a and 01.04.2019 for b) and pick up the balance on that date (500 for a and 750 for b) and add everything. 

What I didn't specify earlier is that I have a date slicer too. So the balance date should be the first date from the slicer date. 

Since this computation is dynamic and should be responsive to a slicer, I should use a measure, not a column. 

But how could I do that?

Your help would be much appreciated:)

[v-evelk]

Well, if I correctly understood, you want to add third column that has to show the sum and this sum calculation is based on the records with earliest dates of every praticular loan. In your example you picked the earliest sum for a (500) and b (750).

Honestly, I am not sure that it is possible to do with any existent visual and it looks like that it is a task for DAX so, I still recommend you to use DAX.

Kind Regards,

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com