Forum Discussion

sryester's avatar
sryester
Copper Contributor
Mar 08, 2022
Solved

Date Relationship in Power Pivot not Recognized

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.

 

DateNAV
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. 

 

DateAccount #Amount
1-Jan-2210000 $          400,000
1-Jan-2210010 $            15,000
1-Jan-2210020 $          (50,000)
11-Jan10040 $            25,000
15-Jan10010 $            15,000
19-Jan10000 $          200,000
21-Jan10040 $            25,000
31-Jan10050 $       (300,000)
01-Feb10010 $            15,000
08-Feb10020 $          (50,000)
15-Feb10010 $            15,000
24-Feb10060 $            75,000
28-Feb10000 $          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.

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    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.

     

Resources