PowerPivot Data Range between two sheets

[successken]

I am working four sheets within PowerPivot: Date Sheet (in order to establish the relationship between the two data sheets), Customer Sheet (a list of unique customer numbers again to further establish the relationship between the two data sheets), Service Data consisting a list of customer numbers, date of service and Sales Data consisting a list of customer numbers and date of sale and Job Type.

From the Service Data Sheet I added a column with the following formula to answer the question whether or not to customer purchased a product after receiving a service for that day and it works fine.

=CALCULATE(COUNTA('Sales Data'[Job Type]),'Sales Data'[Job Type]="Complete")


What I'm looking for is whether or not the customer purchase a product within 30 days from the date of receiving a service. I tried the following formula:

=CALCULATE(COUNTA('Sales Data'[Job Type]),'Sales Data'[Job Type]="Complete",'Sales Data'[orderdate]>('Service Data'[orderdate]-1))

I received the following error:

The expression contains multiple columns, but only a single column can be used in a Boolean expression that is used as a table filter expression.

Any guidance would be greatly appreciated! Thank you!!

 

[masplin]

I think it is some wierdness in the CALCUTLATE filter function that you can only have > a fixed number not another expression i.e. you can only use one column reference in the expression. To get round it try entering a complete filter expression like this:


=CALCULATE(COUNTA('Sales Data'[Job Type]),FILTER('Sales Data','Sales Data'[Job Type]="Complete" && 'Sales Data'[orderdate]>('Service Data'[orderdate]-1)))

Mike

 

[successken]

Thank you Mike. That appeared to have worked.

 

[masplin]

Pretty odd logic!!!