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:

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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 :))
 

Highlander75

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

Tianbas

Board Regular
Joined
Apr 29, 2014
Messages
101
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)

 

Forum statistics

Threads
1,085,834
Messages
5,386,226
Members
401,986
Latest member
crt54

Some videos you may like

This Week's Hot Topics

Top