Power BI Exchange

Expand all | Collapse all

Help needed, investment return timing

  • 1.  Help needed, investment return timing

    Posted 4 days ago
    I am trying to create a chart where I pull data from two tables.
    Investment Table contains the amount invested in a product, the date of the investment, and the product name.
    Returns table contains the product name, the cash flow returns, and the dates of those returns.

    What I'm struggling with is how to join the two tables such that when I produce a report the dates are treated as same type fields, so that if I filter any visual for the product it would show the investments (as negatives, already done) and the returns coming in as positives.

    The modelling section forces me to show the relationships as based on the product name, but then won't seem to let me utilize the date field, to use that as a single date field for both tables.

    thanks for any advice


  • 2.  RE: Help needed, investment return timing

    Bronze Contributor
    Posted 4 days ago
    You can only have a single active relationship between two tables at a time.

    I'd suggest creating a separate calendar table that you relate to both of your existing tables.

    ------------------------------
    Alexis Olson
    DFW, Texas
    ------------------------------



  • 3.  RE: Help needed, investment return timing

    Silver Contributor
    Posted 3 days ago
    Edited by Vishesh Jain 3 days ago
    Hi @Damon Edmondson,

    Even I agree to what @Alexis Olson​​​ said that you need a separate calendar table. There can be only one active relationship between 2 tables.

    Every model needs to have a separate calendar table as the calendar table underlying Power BI which is used as the default calendar table, at times, does not work the way you want it to.

    You can have a look and download the 'Ultimate Calendar Table' by Avi Singh.

    Power BI: The Ultimate Calendar Table (Free Download)
    YouTube remove preview
    Power BI: The Ultimate Calendar Table (Free Download)
    Download Ultimate Calendar Table: http://www.learnpowerbi.com/bonus-download Why is the Calendar table so important in Power BI? And what is the best way to create a Calendar Table? I show you in this video series: *Some videos can only be found inside the Learn Power BI Course.
    View this on YouTube >


    I would suggest you to even have a separate Product table, so that you can slice both the tables by Product.

    Every model needs to have separate tables for Calendar, Product, Region etc. These tables are called the Dimension tables, while the actual 'Sales' table, in your case the 'Investment' and 'Return' tables, are called the Fact tables. That is generally how a data model is designed.

    I hope this will resolve your issue.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 4.  RE: Help needed, investment return timing

    Posted 3 days ago
    ​This was helpful but I'm still a little stuck.

    the calendar date table you provided instructions on your website are very helpful.

    What I have is a dates table (A) (new) with calendar

    a table  (B) that has a product ID code, activity date, and investment amount (expressed as a negative)

    another table (C) with returns which has a product code, activity date, and recovery amount.

    I can join via one to many relationships table A to B on date fields, and A to C on date fields. What I still need to do is join Table B to C on Product ID field. I can't seem to do that. Even if I add a fourth table (D) with product code (and ancillary info like type and color or whatever) it allows me to join one to one table D to Table B, it would let me do a one to many for table D to C...it says that introduces ambiguity between table A and C.

    ------------------------------
    Damon Edmondson
    Chief of Analytics
    Flock Finance
    Roswell GA
    4044339709
    ------------------------------



  • 5.  RE: Help needed, investment return timing

    Top Contributor
    Posted 3 days ago
    Hi Damon,

    Ok, lets follow the steps as mentioned above;

    Table :
    1) Create Dimension table [Calendar] -- which you already did, key column [DateID]
    2) Create Dimension table [Product] -- which you have done, key column [ProductID]
    3) Create Fact table [Investment] which have have done, key column [InvestmentID], other columns [DateID], [ProductID]
    4) Create Fact table [Return] which have have done, key column [ReturnID], other columns [DateID], [ProductID]

    Relationship:
    1) Create 1 to Many relationship [Calendar] - [Investment] based on [DateID]
    2) Create 1 to Many relationship [Calendar] - [Return] based on [DateID]
    3) Create 1 to Many relationship [Product] - [Investment] based on [ProductID]
    4) Create 1 to Many relationship [Product] - [Return] based on [ProductID]

    Expected Behavior:
    When you filter based on [Product] it will filter out investment & return
    When you filter based on [Calendar] it will filter out investment & return
    You can get investment & return for any product at any particular time

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Sr. BI Consultant
    Karachi, Pakistan
    ------------------------------



  • 6.  RE: Help needed, investment return timing

    Silver Contributor
    Posted 2 days ago
      |   view attached
    Hi @Damon Edmondson

    To add to what the others have said, please see if the following helps you. If I have understood your issue correctly, you do not need a relationship between the two transaction tables (Investment Table and Returns Table) to get your calculations. Please see the output, and relationship diagram below.

    thumbnail image
    thumbnail image

     

    I apologize, if I have misunderstood your issue. PFA the pbix file also for your reference.





    ------------------------------
    Gopa Kumar S

    ------------------------------

    Attachment(s)



  • 7.  RE: Help needed, investment return timing

    Silver Contributor
    Posted 2 days ago
    Hi @Damon Edmondson,

    ​I agree to what Hasham said. He has given a step by step approach to what I am trying to convey to you.

    Please try it and if it doesn't work, then kindly upload your file or a dummy file for us to have a look and come up with a solution.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 8.  RE: Help needed, investment return timing

    Silver Contributor
    Posted 2 days ago
    Edited by Vishesh Jain 2 days ago


  • 9.  RE: Help needed, investment return timing

    Posted 2 days ago
    ​Okay so this worked. The key thing I found was that I was incorrectly establishing a one to one relationship on date to investment while having a many to one on return to date. This was causing the error messages I think, and by switching that to many to one, it worked.

    Thanks again to all who helped

    ------------------------------
    Damon Edmondson
    Chief of Analytics
    Flock Finance
    Roswell GA
    4044339709
    ------------------------------



  • 10.  RE: Help needed, investment return timing

    Silver Contributor
    Posted yesterday
    Hi @Damon Edmondson,

    One to one relationship will always give you bi-directional filtering, which is why you might be getting the error.

    Bi-directional filtering is not recommended, unless you really need it.

    Anyways, you got the problem solved and that's what matters.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------