Counting/Referencing multiple date columns against one date table?

Highlander75

New Member
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
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
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.
 

Highlander75

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

Tianbas

Board Regular
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)

 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top