Calc and Sum for multiple tables

[one3nine0]

Sorry, this has probably been hashed out a million times, but I just can't get it to work.

I have two tables Raw Data and Zip Data. Relevant columns are
Raw Data: Location ID, Date, Qualified Calls
Zip Data: Location ID

I want to create a new column in powerpivot to calculate the number of calls per Location ID for between Jan 1, 2014 and Jan 31, 2014 in the Zip data table

This is the formula I came u with
=CALCULATE(SUM(RawData[Qualified Calls]), DATESBETWEEN(RawData[Date], date(2015,01,01),date(2015,01,31)), filter(RawData,RawData[Location ID]=ZipData[Location ID]))

But this returns the sum of all the locations for every single row, instead of by location ID in the ZipData table

What Can I do so that calculate sum, or any calculate formula will filter by location ID???

 

[scottsen]

First off, if you want to use DATESBETWEEN, you will need a "real" calendar table. See Power Pivot Date Table | Power Pivot | Tiny Lizard

2nd, it is not clear to me if there is a relationship between your 2 tables. Is Zip Data a "lookup table" with just unique values?

 

[one3nine0]

Correct, ZipData is just a table with unique values placed in it. I want to add another column with total calls for each location based on the qualified calls column in RawData table.

I know i can do this through sumif in excel, but I want to try to learn to do this through power pivot.

I linked the tables through location ID on both tables through powerpivot.

And if I cannot use the datesbetween function, how do i make sure that the calculate function is only adding from dates in January?

 

[scottsen]

Let's see how close we can get with... not much effort

Create a calculated field (aka "Measure" in 2010)

Total Calls := SUM(RawData[Qualfied Calls])

Create a pivot table with ZipData[Location] on rows, and drop Total Calls in values.

Now you can just add RawData[Date] as a report filter or Slicer and... select the dates you care about.

If really want that in a measure as well... something like:
=CALCULATE([Total Calls], FILTER(RawData, RawData[Date] >= DATEVALUE("1/1/2015") && RawData[Date] <= DATEVALUE("1/31/2015")))

Though... you would be much better off with a calendar table, linked to RawData[Date]... so you can just use your [Total Calls] against Jan 2014, Jan 2015, do YoY% Change, All of 2014, Q1 of 2015... you get the idea

 

[one3nine0]

That worked well, Thanks!

I guess I never got used to using "Measures/Calculated Fields" so approaching it that way is something new.

Thanks again