cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mina2019
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.

DateLoanBalance
01.01.2019a500
01.04.2019a400
01.07.2019a300
01.10.2019a200
01.01.2020a100
01.04.2020a0
01.03.2019b750
01.06.2019b500
01.09.2019b250
01.12.2019b0

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

DateLoanBalanceResult
01.01.2019a5001250
01.04.2019a4001250
01.07.2019a3001250
01.10.2019a2001250
01.01.2020a1001250
01.04.2020a01250
01.03.2019b7501250
01.06.2019b5001250
01.09.2019b2501250
01.12.2019b01250

Thanks in advance

3 REPLIES 3
v-evelk
Microsoft
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

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

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors