Forum Discussion

vivalimon's avatar
vivalimon
Microsoft Employee
4 years ago
Solved

TimeStamped Data - DateDiff for each unique Customer

Hi! I'd like to calculate the time it took for a person to advance from one stage to the next, and filter only those who are advancing. 

 

INPUT

I have a folder of 30+ excel files, each excel file has a huge list of unique customers who are in a certain subscription tier.  

CURRENT (MESSY & SLOW) METHODOLOGY

I combine and load the data into an excel file using PowerQuery, sort by Customer Name, do the calculations there [ (C3-C2) * (A2=A3) * (IF(B2<B3),1,0) ] which returns the date diff only if the stage has advanced & comparing the same stage.

OUTPUT

I'll make charts based on DateDiff and Revenue and other customer info to look for correlations.

 

Is there a way to use DAX to do this with a GROUPBY? Input example below. Thanks in advance!

 

 

  • v-chuncz-msft's avatar
    v-chuncz-msft
    4 years ago

    vivalimon 

     

    You may use the following DAX to add a calculated table.

    Table 2 =
VAR t =
    SUMMARIZE (
        'Table',
        'Table'[Player],
        'Table'[Stage],
        "min Date", MIN ( 'Table'[Date] )
    )
RETURN
    FILTER (
        t,
        RANKX (
            FILTER ( t, 'Table'[Player] = EARLIER ( 'Table'[Player] ) ),
            'Table'[Stage],
            ,
            ASC
        ) > 1
    )

     

1 Reply

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    4 years ago

    vivalimon 

     

    You may use the following DAX to add a calculated table.

    Table 2 =
VAR t =
    SUMMARIZE (
        'Table',
        'Table'[Player],
        'Table'[Stage],
        "min Date", MIN ( 'Table'[Date] )
    )
RETURN
    FILTER (
        t,
        RANKX (
            FILTER ( t, 'Table'[Player] = EARLIER ( 'Table'[Player] ) ),
            'Table'[Stage],
            ,
            ASC
        ) > 1
    )