Forum Discussion

Carla17's avatar
Carla17
Frequent Visitor
4 years ago
Solved

Return values before a selected date in a list + retrieve value from other table based on same date

Hi,

 

I have a double issue and tried multiple solutions 😞 but non of them worked.

 

This is my model:

  1. a list of positions (mainly a selection of stocks with metadata) ISIN is the relevant one because it references to the transaction table and the stock table.
  2. Stocks: historical stock data (linked by ISIN to the positions table)
  3. Transactions: list of transactions (linked by ISIN to the positions table)
  4. Date table: linked to the stocks date and the transaction date

 

 

Issue 1. I want to retrieve all rows before a selected date in a list of dates.

The slices is based on the date table. This list is based on the transactions table. If I select a date in the slicer, is only shows the transactions for that specific date. I would like to show all transactions before or on this date.

 

Issue 2: For the same visual from issue 2: I would like to retrieve the AdjClose value from the stocks table.

 

e.g.: selected date in de slicer is 1/1/2020: the adjClose value from the Stocks table for 1/1/2020 should be shown in de visual.

 

A help is appreciated! 

 

Thanks you very much,

 

Carla

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Carla17 ,

     

    1. I want to retrieve all rows before a selected date in a list of dates.

    I suggest you try before function in Date slicer. If you use List function, your visual will only show the values in selected date.

    2. For the same visual from issue 2: I would like to retrieve the AdjClose value from the stocks table.

    Basic = 
VAR _SELECTDATE =
    MAX ( 'Date'[Date] )
VAR _ADJCLOSE =
    CALCULATE (
        SUM ( Stocks[AdjClose] ),
        FILTER (
            Stocks,
            Stocks[Datum] = _SELECTDATE
                && Stocks[ISIN] = MAX ( Transactions[ISIN] )
        )
    )
RETURN
    IF ( MAX ( Transactions[Datum] ) = _SELECTDATE, _ADJCLOSE, BLANK () )
    adjclose value = SUMX(VALUES(Positions[ISIN]),[Basic])

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Carla17 ,

     

    1. I want to retrieve all rows before a selected date in a list of dates.

    I suggest you try before function in Date slicer. If you use List function, your visual will only show the values in selected date.

    2. For the same visual from issue 2: I would like to retrieve the AdjClose value from the stocks table.

    Basic = 
VAR _SELECTDATE =
    MAX ( 'Date'[Date] )
VAR _ADJCLOSE =
    CALCULATE (
        SUM ( Stocks[AdjClose] ),
        FILTER (
            Stocks,
            Stocks[Datum] = _SELECTDATE
                && Stocks[ISIN] = MAX ( Transactions[ISIN] )
        )
    )
RETURN
    IF ( MAX ( Transactions[Datum] ) = _SELECTDATE, _ADJCLOSE, BLANK () )
    adjclose value = SUMX(VALUES(Positions[ISIN]),[Basic])

    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.