Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

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.

Frequent Visitor

## Portfolio price on date calculate (DAX)

I work on financial assets portfolio analysis project

At current stage I have next tables:

[Calendar] - universal singe calendar with [date], [year], [month] e.t.c fields

[Assets]
• [asset-id]
• [asset-type] - STOCK, CASH, DEPOSIT, e.t.c.
• [account-id]

[Accounts]
• [account-id]
• [currency]
• [balance-on-create]

[Split] - table to store transactions entries
• [split-id]
• [date]
• [account-id]
• [asset-id]
• [transaction-id]
• [start-balance]
• [end-balance] = [start-balance] + [amount]
• [amount]
• [currency] USD, EUR, e.t.c

[Stocks] - stocks prices
• [stock-id]
• [asset-id]
• [date]
• [stock-price]
• [currency] - USD, EUR, e.t.c.

Three purchases of Apple shares for cash are stored as follows:
id     date            account-id    asset-id     start-balance   end-blance     amount currency
3     01.07.2016   2                    7                 400 000            302 500           -97 500     USD
4     01.07.2016   4                    1                 0                       500                  500           USD
15    01.07.2016   2                    7                 302 500            289299             -13 212    USD
16    01.07.2016   4                    1                 500                   600                  100           USD
17    01.07.2016   2                    7                 289 288            250 363            -38925     USD
18    01.07.2016   4                    1                 600                   850                   250          USD

I looking for a measure that calulates price of portfolio on date
So I think I need to create [current-price] measure at [Split] table

I'm try to calculate it through [current-stock-price] at [Stocks] table
current-stock-price =
var d = SELECTEDVALUE('calendar'[Date])
RETURN
CALCULATE (
MIN( stocks[price]);
ALLEXCEPT(stocks;'stocks'[asset-id]);
'stocks'[date] = d
)

But it did not bring results.

What measures I need and how I should reconfig my data model to calculate portfolio price on date?

1 ACCEPTED SOLUTION
Community Support

@lnz,

You may refer to the following example.

http://community.powerbi.com/t5/Desktop/Find-last-value-based-on-date/m-p/293150#M129228

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support

@lnz,

You may refer to the following example.

http://community.powerbi.com/t5/Desktop/Find-last-value-based-on-date/m-p/293150#M129228

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors