Forum Discussion
Helper IIData Model for Stock Data and Portfolio Tracking - Need Guidance
Hi Caz_16,
You can add a calculated column to 'Owned' table to lookup value from 'Price' table abed on 'date' and 'stack id':
ClosePrice =
LOOKUPVALUE (
Price[Close Price],
Price[Stack Id], Owned[Stock ID],
Price[Data Stamp], Owned[Purchase_Date]
)
Regards,
Xiaoxin Sheng
Helper IIAnonymous
Thanks for your reply. That formula helps, but it doesn't really answer my question. I see what it is doing in looking up the price of the stock the day I bought it.
My question really is more about the structure of my data. Is it in a format where I can easily perform calculations between the two tables? I think the answer is no due to the difficulty I'm having in referencing the columns when writing a calc. column or measure.
I'm trying to understand the PBI best practices and make sure that I'm not cutting corners by writing a ton of formulas to force the software into doing something it wasnt designed to do. I want to have a true comprehension in the way that PBI performs calculations and understand the way the best method for building a data model within the software.
- amitchandak
Super User
At a high level, the structure seems fine.
- Please have date table. and Join with your Owned Table. Join of date with price depending on use case
- in the Owned table have a column Amount = Share * cost per Share, prefer column over measure for this
Refer this document
https://docs.microsoft.com/en-us/power-bi/guidance/
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ Hi Caz_16,
Can you please explain more about these? It is hard to share helpful formulas without any detail information.
You can provide the data structure, expected result to help us clarify your requirements.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
