cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vivalimon
Microsoft
Microsoft

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!

 

Untitled.jpg

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors