Value of stock over time with movements and price changes

[Yggdrasill]

I have a model with five tables.

The fact table ('InstrumentTransactions') has dates of purchased or sold quantity of stocks.

The table that registeres value of a stock is in InstrumentExchangeRate table

To calculate the quantity owned at any given time I calculate it as a balance or an inventory like so:

 Qty Running Total = CALCULATE(
    SUM( InstrumentTransaction[Amount] ),
    FILTER(
        ALL( DimTradeDate ),
        DimTradeDate[dimDateKeyAsDate] <= MAX( DimTradeDate[dimDateKeyAsDate] )
    )
)

Where Amount is the actual quantity purchased or sold at DimTradeDate

Now here comes the interesting part and that is to multiply the quantity owned with the latest rate at any given time.

The table 'InstrumentExchangeRate' contains the date and new rate for any ticker. For simplicity we're just going to look at one ticker.

Now I want to calculate, using a DAX measure, the value of ticker A through ALL dates.

For example, I purchase 100 on 1.1.2022 and sell 50 on 24.2.2022 then my data would look like this:
(note my dates are in dd.mm.YYYY)

I for some reason have been unable to come up with a solution on this

[Yggdrasill]

Here's a file with hardcoded examples of how the model looks like and data.

FindLatestValue in SDC table.pbix

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  See the third table int he image.

Hope this helps.

[Yggdrasill]

I'm modifiying the table so it only has max 1 value per day. Look at the InstrumentExchangeRate table as a slowly changing dimension

[Yggdrasill]

The plot thickens - It's a model in Analysis Services. I can't share the file nor use PQ :I

[Ashish_Mathur]

Hi,

Ensure that the dates in your visual are dragged from the Calendar Table.  Try these measures

Price = sum('InstrumentExchangeRate'[Value])

Last known price = 

Latest Balance with formula =

CALCULATE([Price],LASTNONBLANK('Calendar'[Date],CALCULATE([Price])))

Hope this helps.

[Yggdrasill]

Hi, I added a relationship between the calendar table and the exchange table and it works.

Last known price =
VAR _Balance =
    CALCULATE (
        [new Price],
        LASTNONBLANK ( 'DimTradeDate'[dimdatekeyasdate], CALCULATE ( [new Price] ) )
    )
RETURN
    _Balance

Where Price is simply a sum of the exchange rate

But it fails when you have two ore more price changes per day so I did this instead:

Latest Rate = 
CALCULATE (
    MAX ( InstrumentExchangeRate[exchangerate] ),
    FILTER (
        InstrumentExchangeRate,
        InstrumentExchangeRate[dimdatekey] <= MAX ( DimTradeDate[dimdatekey] )
            && InstrumentExchangeRate[dimdatekey] >= MIN ( DimTradeDate[dimdatekey] )
    )
)

Now I just need to calculate the balance over time which should be the QTY Balance * latest price and I'm done.

But I only get this and I need to fill in the gaps

[Ashish_Mathur]

Hi,

In the initial example you took you did not mention that there can be more than 1 price on a day.  Anyways, in the Query Editor, the approach should be to retain only 1 row per day.  If you are amenable to my approach, then share the download link of your PBI file.