Forum Discussion

jaml's avatar
jaml
Regular Visitor
6 years ago
Solved

Comparing stock periods

Hi guys,

I would like your help with a private project of mine. I hope you have some ideas. This is probably very easy for some of you.

Once a week I copy my entire portfolio and insert it into excel and add a timestamp. The I refresh my PowerBI Dekstop Report.


The data in my excel sheet is very simple with the following headers:


Stock name – Currency - Amount of stocks - % Return - % Return in $ - Timestamp - PeriodKey


I would like to see the changes that has happened between timestamps.

Example:

I bought two new stocks between period 17-02-2020 and 18-02-2020.


Result:

Stock name

From period

To period 

Amount

Microsoft

17-02-2020

18-02-2020

+2


If I buy a new stock (with a new stock name) or sell a stock these changes need to be seen as well. Also, it would be great to be able to choose the different periods dynamically.


You got any idea on how to make this happen?


This is my dashboard so far in order for you to see what I have done:

Need Help
Show and Tell
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, jaml 

     

    Based on your description, I created data to reproduce your scenario.

     

    You may create measures as follows.

     

    From period = 
CALCULATE(
    MIN('Table'[Timestamp]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Stock Name] = MAX('Table'[Stock Name])
    )
)
To period = 
CALCULATE(
    MAX('Table'[Timestamp]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Stock Name] = MAX('Table'[Stock Name])
    )
)
Amount = 
var x = [To period]
var y = [From period] 
return
CALCULATE(
    SUM('Table'[Amount of stocks]),
    'Table'[Timestamp] = x
)-
CALCULATE(
    SUM('Table'[Amount of stocks]),
    'Table'[Timestamp] = y
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • jaml's avatar
    jaml
    Regular Visitor
    6 years ago

    To add to this:

    One of my ideas is to create a measure that compares stock amount from period 1 to period 2 - but i have no clue on how to do this when I'm inserting new data on a weekly schedule.



  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, jaml 

     

    Based on your description, I created data to reproduce your scenario.

     

    You may create measures as follows.

     

    From period = 
CALCULATE(
    MIN('Table'[Timestamp]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Stock Name] = MAX('Table'[Stock Name])
    )
)
To period = 
CALCULATE(
    MAX('Table'[Timestamp]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Stock Name] = MAX('Table'[Stock Name])
    )
)
Amount = 
var x = [To period]
var y = [From period] 
return
CALCULATE(
    SUM('Table'[Amount of stocks]),
    'Table'[Timestamp] = x
)-
CALCULATE(
    SUM('Table'[Amount of stocks]),
    'Table'[Timestamp] = y
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • jaml's avatar
      jaml
      Regular Visitor
      6 years ago

      v-alq-msft This is great, thank you so much for your help! Greatly appreciated