Calculate sum of current values according to current date over categories

[Anonymous]

I have a table of loan data for ships, where each ship has an associated loan and loan currency. The loans are paid back by instalments at fixed payment dates. For each ship and for each currency, I want to calculate the total remaining EUR and USD loans at each payment date, either as a measure or column, as shown in the 2 columns on the right.

Ship	Currency	Remaining loan	Payment date	[Total EUR loan]	[Total USD loan]
A	EUR	10 000 000	13.02.2022	10 000 000	4 500 000
A	EUR	9 000 000	13.05.2022	10 500 000	4 500 000
A	EUR	8 000 000	13.08.2022	9 400 000	11 900 000
B	EUR	1 500 000	27.04.2022
B	EUR	1 400 000	27.07.2022
B	EUR	1 300 000	27.10.2022
C	USD	5 000 000	15.01.2021
C	USD	4 500 000	15.01.2022
C	USD	4 000 000	15.01.2023
D	USD	7 400 000	22.07.2022
D	USD	7 200 000	22.10.2022
D	USD	7 000 000	22.01.2023

As you can see, there are both previous and future dates. I have tried using combinations of SUMX, SUMMARIZE, FILTER, MAX, VALUES, LOOKUPVALUE, etc. The idea is to graph the total EUR and USD loan value to see what potential investment opportunities lies ahead. I hope someone is able to help me with this one.

[Anonymous · Accepted solution]

For anyone concerned, I got a working solution from one of our Power BI consultants.

This measure will return the sum of the remaining facility for each loan at any given date (I realize my phrasing was a bit inacurrate in the first post, asking for the remaining facility at each payment date).

Current Remaining Facility Measure = 

VAR selecteddate = MAX ( 'Date Table'[Date] )
VAR facility_vessel =
    CALCULATETABLE (
        SUMMARIZE (
            'Loan Data',
            'Loan Data'[Vessel],
            "remaining_facility",
                VAR lastdata =
                    CALCULATE (
                        MAX ( 'Loan Data'[Payment date] ),
                        'Loan Data'[Payment date] <= selecteddate
                    )
                VAR remaining =
                    CALCULATE (
                        MAX ( 'Loan Data'[Remaining facility] ),
                        'Loan Data'[Payment date] = lastdata
                    )
                RETURN
                    remaining
        ),
        REMOVEFILTERS ( 'Date Table' )
    )
VAR result = SUMX ( facility_vessel, [remaining_facility] )
RETURN
    result

[lbendlin]

Are you concerned about exchange rates?  If so, are they part of your data model?

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Thank you for your reply.

You can find the sample data here: https://we.tl/t-KOHYUbsMTz

What I want is to sum the Remaining Facility at any time. I am not concerned about currency exchange rates.

This is the result I am looking for:

[lbendlin]

I don't know if you actually need to calculate anything explicitly. Would something like this work?

[Anonymous]

I actually need them all summed up in one graph. I have tried to not calculate anything, but that results in this graph, which is not quite what I want:

So for now, I have put together one calculated column for EUR loans and one for USD loans for each date in the Date Table. They look like this:

EUR Loans = 

var VesselB = CALCULATE(LOOKUPVALUE('Loan Data'[Remaining facility],'Loan Data'[Payment date],LASTDATE('Loan Data'[Payment date]),'Loan Data'[Vessel],"Vessel B"),FILTER('Loan Data','Loan Data'[Payment date]<='Date'[Date]&&'Loan Data'[Vessel]="Vessel B"))

var VesselC = CALCULATE(LOOKUPVALUE('Loan Data'[Remaining facility],'Loan Data'[Payment date],LASTDATE('Loan Data'[Payment date]),'Loan Data'[Vessel],"Vessel C"),FILTER('Loan Data','Loan Data'[Payment date]<='Date'[Date]&&'Loan Data'[Vessel]="Vessel C"))

var VesselD = CALCULATE(LOOKUPVALUE('Loan Data'[Remaining facility],'Loan Data'[Payment date],LASTDATE('Loan Data'[Payment date]),'Loan Data'[Vessel],"Vessel D"),FILTER('Loan Data','Loan Data'[Payment date]<='Date'[Date]&&'Loan Data'[Vessel]="Vessel D"))

var VesselE = CALCULATE(LOOKUPVALUE('Loan Data'[Remaining facility],'Loan Data'[Payment date],LASTDATE('Loan Data'[Payment date]),'Loan Data'[Vessel],"Vessel E"),FILTER('Loan Data','Loan Data'[Payment date]<='Date'[Date]&&'Loan Data'[Vessel]="Vessel E"))

return VesselB+VesselC+VesselD+VesselE

USD Loans = 

var VesselA = CALCULATE(LOOKUPVALUE('Loan Data'[Remaining facility],'Loan Data'[Payment date],LASTDATE('Loan Data'[Payment date]),'Loan Data'[Vessel],"Vessel A"),FILTER('Loan Data','Loan Data'[Payment date]<='Date'[Date]&&'Loan Data'[Vessel]="Vessel A"))

var VesselF = CALCULATE(LOOKUPVALUE('Loan Data'[Remaining facility],'Loan Data'[Payment date],LASTDATE('Loan Data'[Payment date]),'Loan Data'[Vessel],"Vessel F"),FILTER('Loan Data','Loan Data'[Payment date]<='Date'[Date]&&'Loan Data'[Vessel]="Vessel F"))

return VesselA+VesselF

This results in this graph, which is more in line with what I want:

Do you see any way I can write this in a more compact manner?

[lbendlin]

Measure = 
var md = max('Loan Data'[Payment date])
var a = ADDCOLUMNS(allselected('Loan Data'[Vessel]),"md",CALCULATE(max('Loan Data'[Payment date]),'Loan Data'[Payment date]<=md))
var b = ADDCOLUMNS(a,"mv",var v=[Vessel] var m=[md] return CALCULATE(max('Loan Data'[Remaining facility]),'Loan Data'[Vessel]=v,'Loan Data'[Payment date]=m))
return sumx(b,[mv])

[Anonymous]

Thank you, that's very good and it works really well looking at each year:

However, looking at each quarter I get some of the same issue, as not every loan has data points for each quarter (bi-annual payments). That's why I tried putting the calculations in the Date table for each date. Do you see any way to implement that in your measure? I have tried to look at it myself, but without luck unfortunately.

 

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins.

[Anonymous]

I will try, thank you.