Hi all,
I am a very experienced Excel user but new to Power Pivot. I am trying to recreate an existing workbook using Power Pivot to reduce memory usage but I am running into an immediate problem with my one-to-many relationship between date columns. I have created an example workbook containing fabricated pertinent information.
In the first table (fNAV) I have the Net Asset Value (i.e. price) of a mutual fund every day.
| Date | NAV |
| 1-Jan-22 | $ 20.00 |
| 2-Jan-22 | $ 19.90 |
| 3-Jan-22 | $ 20.22 |
| 4-Jan-22 | $ 20.62 |
| 5-Jan-22 | $ 20.52 |
| 6-Jan-22 | $ 20.42 |
| 7-Jan-22 | $ 20.34 |
| 8-Jan-22 | $ 20.64 |
| 9-Jan-22 | $ 20.70 |
| 10-Jan-22 | $ 20.72 |
| 11-Jan-22 | $ 20.97 |
| 12-Jan-22 | $ 20.97 |
| 13-Jan-22 | $ 20.91 |
| 14-Jan-22 | $ 21.14 |
| 15-Jan-22 | $ 21.33 |
| 16-Jan-22 | $ 21.48 |
| 17-Jan-22 | $ 21.89 |
| 18-Jan-22 | $ 22.13 |
| 19-Jan-22 | $ 22.28 |
| 20-Jan-22 | $ 22.19 |
| 21-Jan-22 | $ 22.15 |
| 22-Jan-22 | $ 22.33 |
| 23-Jan-22 | $ 22.62 |
| 24-Jan-22 | $ 22.66 |
| 25-Jan-22 | $ 22.93 |
| 26-Jan-22 | $ 23.35 |
| 27-Jan-22 | $ 23.49 |
| 28-Jan-22 | $ 23.89 |
| 29-Jan-22 | $ 24.27 |
| 30-Jan-22 | $ 24.51 |
| 31-Jan-22 | $ 24.93 |
| 1-Feb-22 | $ 24.90 |
| 2-Feb-22 | $ 24.73 |
| 3-Feb-22 | $ 24.93 |
| 4-Feb-22 | $ 24.88 |
| 5-Feb-22 | $ 25.17 |
| 6-Feb-22 | $ 25.45 |
| 7-Feb-22 | $ 25.88 |
| 8-Feb-22 | $ 26.14 |
| 9-Feb-22 | $ 26.06 |
| 10-Feb-22 | $ 25.88 |
| 11-Feb-22 | $ 26.37 |
| 12-Feb-22 | $ 26.45 |
| 13-Feb-22 | $ 26.29 |
| 14-Feb-22 | $ 26.11 |
| 15-Feb-22 | $ 26.19 |
| 16-Feb-22 | $ 26.48 |
| 17-Feb-22 | $ 26.82 |
| 18-Feb-22 | $ 26.85 |
| 19-Feb-22 | $ 26.74 |
| 20-Feb-22 | $ 27.14 |
| 21-Feb-22 | $ 27.47 |
| 22-Feb-22 | $ 27.58 |
| 23-Feb-22 | $ 27.77 |
| 24-Feb-22 | $ 27.94 |
| 25-Feb-22 | $ 28.08 |
| 26-Feb-22 | $ 28.50 |
| 27-Feb-22 | $ 28.90 |
| 28-Feb-22 | $ 28.81 |
In the second table (fTransactions) I have a list of client transactions. Each row has a date, the client account number, and the dollar value of their contribution (redemption). Some days there are multiple transactions, some days there are none.
| Date | Account # | Amount |
| 1-Jan-22 | 10000 | $ 400,000 |
| 1-Jan-22 | 10010 | $ 15,000 |
| 1-Jan-22 | 10020 | $ (50,000) |
| 11-Jan | 10040 | $ 25,000 |
| 15-Jan | 10010 | $ 15,000 |
| 19-Jan | 10000 | $ 200,000 |
| 21-Jan | 10040 | $ 25,000 |
| 31-Jan | 10050 | $ (300,000) |
| 01-Feb | 10010 | $ 15,000 |
| 08-Feb | 10020 | $ (50,000) |
| 15-Feb | 10010 | $ 15,000 |
| 24-Feb | 10060 | $ 75,000 |
| 28-Feb | 10000 | $ 150,000 |
In the pivot table, I am trying to show each transaction a particular client made, and I'm trying to pull the respective NAV from that date. The problem I'm having is my pivot table can't seem to look up a specific NAV from a specific transaction date, and instead it sums all the NAVs from the fNAV table.
In the data model manager, I have created a relationship between the date column in fNAV (one) to the date column in fTransactions (many).
But I am still getting the error that reads "Relationships between tables may be needed."
In the data model manager, I have confirmed that the Data Type is Date for each column and the time stamps are all set at 12:00:00 AM.
As a sanity check, I replaced the Date in the pivot table with the dates from fNAV and the relationship appears to be working despite still getting that error. However, this doesn't really help me.
Am I doing something wrong? Or am I simply misunderstanding the relationship function? I'm happy to upload the sample workbook but I can't quite figure out how to do that.
Thanks in advance for any help.
@sryester You need to create a measure that allows the Account# to cross from the many-side to the 1-side. You can do that by using CROSSFILTER.
=CALCULATE( SUM( [NAV] ), CROSSFILTER ( fTransactions[Date],fNAV[Date], Both ) )
Attached a workbook demonstrating this, though with different table names and an interim step in Power Query as I copied your data from the forum into Excel and needed to clean it up first.
Accepted Solutions
@sryester You need to create a measure that allows the Account# to cross from the many-side to the 1-side. You can do that by using CROSSFILTER.
=CALCULATE( SUM( [NAV] ), CROSSFILTER ( fTransactions[Date],fNAV[Date], Both ) )
Attached a workbook demonstrating this, though with different table names and an interim step in Power Query as I copied your data from the forum into Excel and needed to clean it up first.