Counting/Referencing multiple date columns against one date table?

Highlander75

New Member
Joined
Sep 18, 2014
Messages
7
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
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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 :))
 
Upvote 0
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.
 
Upvote 0
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)

 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top