none
DATESYTD (or any Time Intelligent Function) Working Off Other Data Besides Date in the Pivot Table

    Question

  • I am working on a project where I need to count the number of events from the start of the year to the end for each year in the data with each event counting the current event and any previous events sequentially.  My results should look like the following:

    MarketNameDate MarketCount
    2012-09-11|Kansas City 1
    2012-09-18|Denver 2
    2012-09-25|Chicago 3
    2012-10-02|Seattle 4
    2012-10-09|Phoenix 5
    2012-10-16|Tampa 6
    2012-10-23|Norfolk 7
    2012-10-30|Alexandria 8
    2012-11-06|San Jose 9
    2012-11-27|Dallas 10
    2012-12-04|Fort Lauderdale 11
    2013-01-15|Los Angeles 1
    2013-01-22|Orange County 2

    My formula is as follows :=calculate(distinctcount(Event_Table[MarketNameDate]),datesytd(dates_dimensions[date_value]),all(dates_dimensions)).  This formula works correctly when I am using the Date_Dim table to drive the analysis in the Row Label or Column Label in my Pivot Table.  My events table is connected directly to Date_Dim table and I would like to use the [MarketNameDate] field instead. 

    I need to get a rolling count of the number of markets through the year, but roll up on [MarketNameDate] instead of anything out of Date_Dim.  Is this possible or do the Time Intelligence functions require you to only use Date_Dim columns as Row Label or Column Label in my Pivot Table.

    I would also be happy to have an alternative way of solving the problem if there is a way to do it.

    Here is an approximation of the data I am using.

    Seminar_Events
    Event_Type Market MarketNameDate MarketMinDate
    Preview Kansas City 2012-09-11|Kansas City 9/11/2012 0:00
    Workshop Kansas City 2012-09-11|Kansas City 9/11/2012 0:00
    Preview Denver 2012-09-18|Denver 9/18/2012 0:00
    Workshop Denver 2012-09-18|Denver 9/18/2012 0:00
    Preview Chicago 2012-09-25|Chicago 9/25/2012 0:00
    Workshop Chicago 2012-09-25|Chicago 9/25/2012 0:00
    Preview Seattle 2012-10-02|Seattle 10/2/2012 0:00
    Workshop Seattle 2012-10-02|Seattle 10/2/2012 0:00
    Preview Phoenix 2012-10-09|Phoenix 10/9/2012 0:00
    Workshop Phoenix 2012-10-09|Phoenix 10/9/2012 0:00
    Preview Tampa 2012-10-16|Tampa 10/16/2012 0:00
    Workshop Tampa 2012-10-16|Tampa 10/16/2012 0:00
    Preview Norfolk 2012-10-23|Norfolk 10/23/2012 0:00
    Workshop Norfolk 2012-10-23|Norfolk 10/23/2012 0:00
    Preview Alexandria 2012-10-30|Alexandria 10/30/2012 0:00
    Workshop Alexandria 2012-10-30|Alexandria 10/30/2012 0:00
    Preview San Jose 2012-11-06|San Jose 11/6/2012 0:00
    Workshop San Jose 2012-11-06|San Jose 11/6/2012 0:00
    Preview Dallas 2012-11-27|Dallas 11/27/2012 0:00
    Workshop Dallas 2012-11-27|Dallas 11/27/2012 0:00
    Preview Fort Lauderdale 2012-12-04|Fort Lauderdale 12/4/2012 0:00
    Workshop Fort Lauderdale 2012-12-04|Fort Lauderdale 12/4/2012 0:00
    Preview Los Angeles 2013-01-15|Los Angeles 1/15/2013 0:00
    Workshop Los Angeles 2013-01-15|Los Angeles 1/15/2013 0:00
    Preview Orange County 2013-01-22|Orange County 1/22/2013 0:00

    Date_Dimensions
    date_value
    1/1/2012 0:00
    1/2/2012 0:00
    1/3/2012 0:00
    1/4/2012 0:00
    1/5/2012 0:00
    ETC

    There is a relationship created between Date_Dimensions[Date_Value] and Seminar_Events[MarketDateName]

    Any help would be very much appreciated.

    Kind Regards,

    John


    Thursday, January 09, 2014 11:55 PM

Answers

  • If you want a running count on MarketNameDate, you need to create a numeric ranking of that column in a new calculated column in your Events table.

    Something like:

    Rank = RANKX(ALL(Events), [MarketNameDate], ,1)

    You should also add a year calculated column in that table:

    Year = YEAR([MarketMinDate])

    Once those are in place, this measure should work for you:

    MarketCount:=
    CALCULATE(
           DISTINCTCOUNT(
                 Events[MarketNameDate]
           ), 
           FILTER(
                 ALL(Events), 
                 Events[Rank] <= MAX(Events[Rank])
                 && Events[Year] = MAX(Events[Year])
           )
      )


    Tuesday, January 14, 2014 5:50 PM

All replies

  • If you want a running count on MarketNameDate, you need to create a numeric ranking of that column in a new calculated column in your Events table.

    Something like:

    Rank = RANKX(ALL(Events), [MarketNameDate], ,1)

    You should also add a year calculated column in that table:

    Year = YEAR([MarketMinDate])

    Once those are in place, this measure should work for you:

    MarketCount:=
    CALCULATE(
           DISTINCTCOUNT(
                 Events[MarketNameDate]
           ), 
           FILTER(
                 ALL(Events), 
                 Events[Rank] <= MAX(Events[Rank])
                 && Events[Year] = MAX(Events[Year])
           )
      )


    Tuesday, January 14, 2014 5:50 PM
  • Answers the question beautifully.  Thanks very much!!!
    Thursday, January 16, 2014 4:00 PM
  • If you want a running count on MarketNameDate, you need to create a numeric ranking of that column in a new calculated column in your Events table.

    Something like:

    Rank = RANKX(ALL(Events), [MarketNameDate], ,1)

    You should also add a year calculated column in that table:

    Year = YEAR([MarketMinDate])

    Once those are in place, this measure should work for you:

    MarketCount:=
    CALCULATE(
           DISTINCTCOUNT(
                 Events[MarketNameDate]
           ), 
           FILTER(
                 ALL(Events), 
                 Events[Rank] <= MAX(Events[Rank])
                 && Events[Year] = MAX(Events[Year])
           )
      )


    Mike, can you check out this thread? http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a2e22d61-7c32-43f2-900e-d9d6325fa26d/needed-more-answerers?forum=sqlkjpowerpivotforexcel

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, January 17, 2014 7:43 PM