Forum Discussion

wknelson's avatar
wknelson
Advocate II
8 years ago

Dynamic Table Share Prices

I have a share price table (marketPricing) that has share prices by account number and date. I would like to use a DateValue filter to create a list of share prices by account based on the max date of the DateValue filter. I would then like to use these values in a table of investment funds in order to create historical reports based on the max date selected with the DateValue filter. By using the following measure I can obtain the share price for the DateValue -

 

Current Share Price =
CALCULATE (
      SUM ( marketPricing[PRICE] ),
      LASTNONBLANK (
               'Date_Slicer'[DateValue],
               CALCULATE ( SUM ( marketPricing[PRICE] ) )
     )
)

 

Output example:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In this example the last share price is $42.74 but when I use the measure value in the investment fund table it varies by transaction date i.e., not the value $42.74. Ideas on how to make this a fixed value based only on the DateValue filter?

 

Thanks!

 

Need Help

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi wknelson

     

    Try removing the filter context from the transaction date column.  I don't know what table your transaction date is on, so please adjust accordingly.  If you add a filter condtion as shown in red, how do you get on?

     

    Current Share Price =
CALCULATE (
      SUM ( marketPricing[PRICE] ),
      ALL(marketPricing[Date of Transaction],marketPricing[Price]) , 
      LASTNONBLANK (
               'Date_Slicer'[DateValue],
               CALCULATE ( SUM ( marketPricing[PRICE] ),ALL('marketPricing'[Date of Transaction]) )
     )
)

     

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      Here is an alternative

       

      Current Share Price = 
VAR LastDateWithPrice = 
    LASTNONBLANK(
        'Date_Slicer'[DateValue],
        'marketPricing'[Current Share Price]
        )
RETURN 
    CALCULATE(
        SUM('marketPricing'[Price]),
        'marketPricing'[Date of Transaction] = LastDateWithPrice
        )
      • wknelson's avatar
        wknelson
        Advocate II
        8 years ago

        Phil_Seamark Thanks for your replies!

        Removing the filter context from the transaction date resulted in the same value. The alternative didn't work as measure Current Share Price wouldn't accept having the same measure name in the formula

         'marketPricing'[Current Share Price]

        I've made a table Market Value Table as a list of all shares and just the current price in hopes that might resolve the issue but that hasn't worked as well. When using measures to populate it I have correct figures. When trying to create calculated columns (joined with the Investment Fund table as set values - not time-sensitive) the values are incorrect - all the same as no filter context. Below is a screenshot of what I am attempting -

        I'm also including my modeling in case that might be an issue -

         

         

         

         Thanks!