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 proble...
BI & Data Analysis
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Mar 08, 2022

    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
Mar 08, 2022

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.

 

sryester's avatar
sryester
Copper Contributor
Mar 09, 2022

Riny_van_Eekelen It works!  Thank you so much.. wish I came here a lot sooner.

Resources