cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vkong6m
Frequent Visitor

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

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.

 

vkong6m_0-1656376886630.png

 

 

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!

 

1 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
    

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

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
Frequent Visitor

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. 

vkong6m_0-1656445066961.png

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. 

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
Frequent Visitor

I cannot believe this is actually working 😲

 

Thank you! This helped more than you know.

 

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors