Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have a situation where i have a table with entities with different stages, completion status, and status dates. I'm trying to calculate the average days spent between status changes (e.g. average time to move from status 1 to status 2). I was able to figure out a method to use calculate tables to calculate counts of entities completing multiple stages. I have attached a a picture below for details
Appreciate any help with this
Regards
Hari
Solved! Go to Solution.
Hi @hariganesh,
Please try a measure like below.
Measure = VAR newCompletedDate = IF ( ISBLANK ( MAX ( 'Table1'[Completed Date] ) ), TODAY (), MIN ( 'Table1'[Completed Date] ) ) VAR preDate = CALCULATE ( MAX ( 'Table1'[Completed Date] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Completed Date] < newCompletedDate && 'Table1'[Entity] = MAX ( [Entity] ) ) ) RETURN DATEDIFF ( preDate, newCompletedDate, DAY )
Measure 2 = AVERAGEX ( SUMMARIZE ( 'Table1', Table1[Entity], Table1[Event], "Value", [Measure] ), [Value] )
Best Regards,
Dale
Hi @hariganesh,
Please try a measure like below.
Measure = VAR newCompletedDate = IF ( ISBLANK ( MAX ( 'Table1'[Completed Date] ) ), TODAY (), MIN ( 'Table1'[Completed Date] ) ) VAR preDate = CALCULATE ( MAX ( 'Table1'[Completed Date] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Completed Date] < newCompletedDate && 'Table1'[Entity] = MAX ( [Entity] ) ) ) RETURN DATEDIFF ( preDate, newCompletedDate, DAY )
Measure 2 = AVERAGEX ( SUMMARIZE ( 'Table1', Table1[Entity], Table1[Event], "Value", [Measure] ), [Value] )
Best Regards,
Dale
Thank you, i ended up using a different method as the stages were dynamic and could grow,
For clarification, I'm looking to see if there's a way to calculate the average days between a set of stages (e.g. stage1-2, Stage 2-3) for completed entries as a measure.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |