Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
5 | |
1 | |
1 | |
1 | |
1 |