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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lnz
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
v-chuncz-msft
Community Support
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.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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