Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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
)
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
)
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
7 |