PowerPivot Transactions per Day Analysis

[spalmer]

Hi,

I'm attemting to do a transaction analysis using PP of how many transactions occured per day. I have my data linked to a calendar table but when I choose the calendar columns of Year, month and day the pivot table is blank. I added calculated columns in my Budget data table for year month and day and when I choose them for the rows then the data shows up. I'm totally confused!

I have uploaded a copy of my spreadsheet here - https://dl.dropboxusercontent.com/u/45530070/PI Transaction Analysis 2013-08-10.xlsx

The challenge is that I have 6 other data tables linked to the calendar lookup table that I want to show in columns such that over all the different types of transactions I will get a grand total of how many transactions occurred per day.

I'm relatively new to PP and was hoping this would be an easy start to learning about this powerful tool. I have watched a lot of you tube and also bought Rob Collier's book and for some reason I'm totally stumped. Very frustrating when I know it's probably something really silly that I'm NOT doing right!

I'm ready to give up and do the analysis within excel.

I'm desperate! Please help me stay on the PowerPivot line!

Thanks in advance,
Suzi

 

[powerpivotpro]

Hi Suzi. I just looked at your workbook. Your budget table has date/time values in it like 1/11/2012 12:06:08 PM and your calendar table lacks times (they show up as 12:00 am which is the same as lacking a time).

So the relationship between your two tables IS in place, but there are ZERO matching values between Budget and Calendar. I created a new calc column in your Budget table with the formula =DATE(YEAR([Creation Date]), MONTH([Creation Date]), DAY([Creation Date])) - that strips off the time values.

Then change your relationship to link based on that new column, not [Creation Date]

Should be good to go

 

[spalmer]

WOW!! Now that DOES make a difference! I should have come to you earlier! When you put it in those terms it makes absolute sense! Thanks so much Rob! I loved your book as well and i will continue to Study it!

I may be back!

Thanks again,
Suzi