Need sum of CLOSINGBALANCE for category in related table (currently showing blank)

[vkong6m]

Hi,

I have a working measure to calculate my ending balance of each investment but I need help in modifying it so it can calculate across categories.

In this dummy data set I have 3 investments - Happy, Sad, and Funny. The investment values are kept in'Investment Data'[Investment Value], which is a measure that is sliceable by time.

Example At June 30, 2022:

Funny is worth $0.70mil

Happy is worth $1.8mil

Sad is worth $1.77mil

Happy and Sad are both in the USA, so I'd like to have a visual that sums these two amounts to $3.57mil whenever I have USA as a region filtered. At present my measure is only working at the individual investment level, not the category level.

As you can see above only Canada is showing an Investment Value presumably because in this data set there is only one investment (Funny) in Canada. USA has two investments and is showing blank.

Please note this is a dummy data set and in reality I have a long list of investments, so I can't specifically type the investment names in my DAX formula.

The pbix is here: https://drive.google.com/drive/folders/11WZKuWiNRz7vYAKmY0SP0gRwj8-qWJjo?usp=sharing

Your help would be greatly appreciated. Thank you.

[v-rzhou-msft · Accepted solution]

Hi @vkong6m ,

I suggest you to try this code to create a measure.

Measure = 
VAR _SUMMARIZE = SUMMARIZE('Investment Data',[Investment],[Region],

"Investment Value ",

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

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

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

VAR
    closingBalance = openingBalance + totalPurchases + totalSales

RETURN

closingBalance
    
)
RETURN
SUMX(_SUMMARIZE,[Investment Value ])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vkong6m]

Hi @v-rzhou-msft , thank you, I really appreciate your help

Could you please help me modify this measure as now my "market revaluation" is in a separate table from purchase and sales? Everything is the same, just the revaluation column that was in 'Investment Data' is now in a separate table. I can't seem to do it. Updated pbix and excel here: https://drive.google.com/drive/folders/11WZKuWiNRz7vYAKmY0SP0gRwj8-qWJjo?usp=sharing

[v-rzhou-msft]

Hi @vkong6m ,

I have checked the data model in your sample file V4. I think it is not a good way to only put "market revaluation" into a separate table. I think you will determind market revaluation by Investment and Date. This may make your calculation complex. I suggest you to try the data model in V3.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.