locked
Find all activity from a new entity after a given date RRS feed

  • Question

  • I have table that lists activities from all customer accounts. I want to isolate all activity from just new customers after say a launch event. In other words I want to narrow down all the new customers that have joined after an event and all the activity from these new customers.

    For this I'm trying to create a column say 'ActivityFromNewCustomersSinceEvent' and have that marked '1' where the above condition is true.

    What's the best way to do this assuming I have these columns to begin with

    ActivityId, ActivityTime, AccountID, ActivityValue


    Thursday, November 26, 2015 8:09 AM

Answers

  • I'm assuming that a AccountID = customer and therefore that you are looking for AccountIds that only have activity after a given date. Assuming this is true something like the following should work (you'll need to change the date() function in bold to use your specific date) :

    =
    IF (
        MINX (
            FILTER (
                ALL ( Activity ),
                Activity[AccountID] = EARLIER ( Activity[AccountID] )
            ),
            Activity[ActivityTime]
        )
            > DATE ( 2015, 11, 4 ),
        "NEW",
        "EXISTING"
    )
     

    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Michael Amadi Friday, November 27, 2015 7:14 AM
    • Marked as answer by Charlie Liao Tuesday, December 8, 2015 9:21 AM
    Thursday, November 26, 2015 8:12 PM
    • Proposed as answer by Michael Amadi Friday, November 27, 2015 7:14 AM
    • Marked as answer by Charlie Liao Tuesday, December 8, 2015 9:21 AM
    Friday, November 27, 2015 2:59 AM

All replies

  • I'm assuming that a AccountID = customer and therefore that you are looking for AccountIds that only have activity after a given date. Assuming this is true something like the following should work (you'll need to change the date() function in bold to use your specific date) :

    =
    IF (
        MINX (
            FILTER (
                ALL ( Activity ),
                Activity[AccountID] = EARLIER ( Activity[AccountID] )
            ),
            Activity[ActivityTime]
        )
            > DATE ( 2015, 11, 4 ),
        "NEW",
        "EXISTING"
    )
     

    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Michael Amadi Friday, November 27, 2015 7:14 AM
    • Marked as answer by Charlie Liao Tuesday, December 8, 2015 9:21 AM
    Thursday, November 26, 2015 8:12 PM
    • Proposed as answer by Michael Amadi Friday, November 27, 2015 7:14 AM
    • Marked as answer by Charlie Liao Tuesday, December 8, 2015 9:21 AM
    Friday, November 27, 2015 2:59 AM