cancel
Showing results for
Did you mean:
Frequent Visitor

Sum of values based on filters for 2 other columns

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

3 REPLIES 3
Microsoft

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

Frequent Visitor

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:)

Microsoft

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

Announcements

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.