Creating A Pivot Of Two Tables Using Date As The Primary Key

[jkancler]

I have two tables that are linked by Date using the DateStream table from the Azure Marketplace.


Data_WK_2014 table contains NET, COGS with date format: 5/7/2014 12:00:00 AM


Data_WK_Spend table contains MARKETINGSOURCE, SPEND with date format: 5/7/2014 12:00:00 AM


These two tables are linked to DATEKEY in the DateStream table, date format: 5/7/2014 12:00:00 AM


When I create a pivot table using Date in the row lable it will only sum values from one table. For example NET and COGS
are broken out by date however SPEND is shown in the aggregate GRAND TOTAL and not broken out by date. How do I break out spend by date along with net and cogs?

 

[scottsen]

I suspect you aren't using the date from your DATE TABLE ​on rows.

 

[jkancler]

Just added the Date from DATE TABLE to the row and received this error message:

[TABLE="width: 1188"]
<tbody>[TR]
[TD="width: 1188"]ERROR - CALCULATION ABORTED: The expression referenced a relationship between 'Data_WK_Spend'[Date] and 'Date'[DateKey], which does not hold any data because it needs to be recalculated.

Not sure what that means. [/TD]
[/TR]
</tbody>[/TABLE]

 

[jkancler]

Doh! I had it running in manual calculation mode. Just turned on automatic and the pivot table updated with the spend broken out by DATE. Thanks!

Just added the Date from DATE TABLE to the row and received this error message:

[TABLE="width: 1188"]
<tbody>[TR]
[TD="width: 1188"]ERROR - CALCULATION ABORTED: The expression referenced a relationship between 'Data_WK_Spend'[Date] and 'Date'[DateKey], which does not hold any data because it needs to be recalculated.

Not sure what that means.[/TD]
[/TR]
</tbody>[/TABLE]