# Thread: Counting/Referencing multiple date columns against one date table? Thanks: 0 Likes: 0

1. ## Counting/Referencing multiple date columns against one date table?

Hi

Apologies for maybe not using the right terminology (New to powerpivot), but I would appreciate help with the following:

I have the following scenario example:

Table 1:
Product StartDate End Date
A 1 Jan 30 Jan
B 1 Jan 1 Apr
C 1 Feb 30 Mar
D 1 Mar 1 Apr

Table 2
Date Month
1 Jan Jan
30 Jan Jan
1 Feb Feb
1 Mar Mar
1 Apr Apr

I am trying to get the following pivot table result: (Where I count tx in the month the transaction falls in)

Jan Feb Mar Apr
StartDate 2 1 1 0
EndDate 1 0 1 2

My problem seems to stem from trying to use 2 different date columns?

Any solutions?

Much appreciated

2. ## Re: Counting/Referencing multiple date columns against one date table?

Apologies for the formatting. should have used proper table

3. ## Re: Counting/Referencing multiple date columns against one date table?

Between any two tables (Calendar/Date table and Products table, in your case), you may only have 1 active relationship.

So, you can create 1 active relationship on Start Date, and one INACTIVE on End Date. Then you have to use DAX magic to activate the relationship:

Products Starting := SUM(Products)
Products Ending := CALCULATE(SUM(Products), USERELATIONSHIP(Products[End Date], Calendar[Date]))

(Or something like... it's not like I launched excel )

4. ## Re: Counting/Referencing multiple date columns against one date table?

Thanks- I still seem to be missing something, as I can't get it to work. If I use the Sum(Product) formula it asks me to specify the column. Do I in both instances use the starting date column?
Then do I put the two formulas into the values field? It doesn't appear to allow me to put it anywhere else. Sorry, my dax knowledge is almost non-existent.

5. ## Re: Counting/Referencing multiple date columns against one date table?

Got it to work. Fiddled a bit with the above suggestion. Thanks for the help scottsen.

6. ## Re: Counting/Referencing multiple date columns against one date table?

another way for those that work with the old Powerpivotversion without USERELATIONSHIP

Add two calculated columns with start month and end month in the product table.

Formula for the two measures in the pivot would be
Products Starting = iferror(countrows(Filter(Product,Product[Start Month]=values(Period[Month]))),0)
Products Ending = iferror(countrows(Filter(Product,Product[End Month]=values(Period[Month]))),0)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•