Closing balance to include latest opening balance plus sum of subsequent activities until some date

[vkong6m]

Hi PBI gurus!

Thanks in advance for looking at this problem. My team and I have hired a PBI consultant but this stumped him as well. XLS and PBIX here: https://drive.google.com/drive/folders/11WZKuWiNRz7vYAKmY0SP0gRwj8-qWJjo?usp=sharing

Below is a set of mock investment data in one single table. For each Investment, we can buy shares (Purchase) or sell shares (Sale). From time to time the value of the entire Investment is revalued (Market Revaluation); not all Investments gets revalued however. The challenge for me is the Purchase and Sales amounts are marginal/transactional amounts, whereas the Market Revaluation is a balance at a moment in time.

Goal: The reader is able to select an ending date, and the visual (bar chart or card) shows the total value of that Investment at that date.

Example 1: How much is Happy Investment worth at January 3rd, 2022?

The solution should add the latest Market Revaluation at Dec 31, 2021 (opening balance) to any purchases and sales subsequent to that but not to exceed the specified date of January 3rd. In this case on January 1st, 2022 we bought $40,041 worth of Happy Investment, which should be added.

921,198 + 40,041 = 961,239

Example 2: How much is Sad Investment worth at Jun 27, 2022?

Since there is no Market Revaluation before Jun 27, 2022, the total value should aggregate all Purchase and Sales until that date (ie. opening balance is 0).

1,700,000 - 61,877 + 49,570 = 1,687,693

What I have tried after spending time on these forums, google and youtube:

1. using Calculate(Sum(Filter w/ Earlier to get running totals of Purchases or Sales by Investments, but I cannot do the same for Market Revaluation
2. Using VAR MAXX(Filter( to set a Max Date to get the latest Market Revaluation, but I don't know how to add the Purchases and Sales after that date. I am absolutely stumped here!

[johnt75]

Set up a date table but don't link it to the data table. Then try

Investment Value = 
var maxDate = MAX('Date'[Date])
var currentInvestment = SELECTEDVALUE( 'Sheet1'[Investment] )
var lastRevaluation = CALCULATETABLE( TOPN(1, 'Sheet1', 'Sheet1'[Activity Date]),
    REMOVEFILTERS('Sheet1'),
    'Sheet1'[Investment] = currentInvestment,
    'Sheet1'[Activity Type] = "Market Revaluation",
    'Sheet1'[Activity Date] <= maxDate
)
var openingBalance = SELECTCOLUMNS( lastRevaluation, "@val", [Market Revaluation])
var openingBalanceDate = SELECTCOLUMNS( lastRevaluation, "@val", [Activity Date])
var totalPurchases = CALCULATE( SUM(Sheet1[Purchase Amount]), 
    REMOVEFILTERS( 'Sheet1'),
    'Sheet1'[Activity Type] = "Purchase",
    'Sheet1'[Investment] = currentInvestment,
    'Sheet1'[Activity Date] > openingBalanceDate && 'Sheet1'[Activity Date] <= maxDate
)
var totalSales = CALCULATE( SUM(Sheet1[Sale Amount]), 
    REMOVEFILTERS( 'Sheet1'),
    'Sheet1'[Activity Type] = "Sale",
    'Sheet1'[Investment] = currentInvestment,
    'Sheet1'[Activity Date] > openingBalanceDate && 'Sheet1'[Activity Date] <= maxDate
)
var closingBalance = openingBalance + totalPurchases + totalSales
return closingBalance

[vkong6m]

Thanks Johnt75, took me awhile to dissect what you are doing (I'm a beginner) but I see the logic now.

I'm getting an error when I plop [Investment Value] into any visual. 

I think it's because the Investment field is a text field?

I've saved my attempt in this (2022.06.22 response to johnt) folder: https://drive.google.com/drive/folders/11WZKuWiNRz7vYAKmY0SP0gRwj8-qWJjo?usp=sharing

Also I've added in an independent date table as you suggested. 

[johnt75 · Accepted solution]

You have a typo at line 22, you're comparing 'Data'[Activity Date] instead of 'Data'[Activity Type].

It should be

Investment Value = 

VAR
    maxDate = max('date 1'[Date 1])
VAR
    currentInvestment = SELECTEDVALUE('Data'[Investment])
VAR
    lastRevaluation = calculatetable ( TOPN( 1, 'Data', 'Data'[Activity Date] ),
    REMOVEFILTERS(Data),
        Data[Investment] = currentInvestment,
        Data[Activity Type] = "Market Revaluation",
        Data[Activity Date] <= maxDate
    )

VAR 
    openingBalance = SELECTCOLUMNS(lastRevaluation, "@val", [Market Revaluation])
VAR
    openingBalanceDate = SELECTCOLUMNS( lastRevaluation, "@val", [Activity Date])
VAR
    totalPurchases = CALCULATE( sum(Data[Purchase Amount]),
    REMOVEFILTERS('data'),
        Data[Activity Type] = "Purchase",
        Data[Investment] = currentInvestment,
        Data[Activity Date] > openingBalanceDate && Data[Activity Date] <= maxDate
    )

VAR
    totalSales = CALCULATE( sum(Data[Sale Amount]),
    REMOVEFILTERS('Data'),
        Data[Activity Type] = "Sale",
        Data[Investment] = currentInvestment,
        Data[Activity Date] > openingBalanceDate && Data[Activity Date] <= maxDate
    )

VAR
    closingBalance = openingBalance + totalPurchases + totalSales

RETURN

closingBalance
    

[vkong6m]

I cannot believe this is actually working 😲

Thank you! This helped more than you know.