Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by Highlander75; Sep 18th, 2014 at 08:46 AM.

  2. #2
    New Member
    Join Date
    Sep 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting/Referencing multiple date columns against one date table?

    Apologies for the formatting. should have used proper table

  3. #3
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 )
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  4. #4
    New Member
    Join Date
    Sep 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    New Member
    Join Date
    Sep 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    Board Regular
    Join Date
    Apr 2014
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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