Forum Discussion
Calculate Phase/Stage duration
Hi Anonymous ,
Is it this you are looking for?
I solved it in two steps with two different calculated columns.
First, we need to create an index column so we can iterate through each row and calculate the difference between their respective dates. Since we have rows which have the same date, we do need to have a second column deciding which one to rank first or second. In your case, I chose to_stage_id, so the ranking looks like this:index = VAR MaxToStageID = MAX( 'Table'[to_stage_id] ) VAR result = RANKX ( ALL ( 'Table' ), 'Table'[activity_created] * MaxToStageID + 'Table'[to_stage_id], , ASC, DENSE ) RETURN resultThe guys from sqlbi have done a blog about this topic on how to rank / create indexes on multiple columns.
After that, I created another calculated column which uses the index to iterate through the table calculating the date difference:
tomstest = VAR Index = ('Table'[index]) VAR PreviousIndex = ('Table'[index] - 1 ) VAR result = DATEDIFF( CALCULATE ( VALUES ( 'Table'[activity_created] ), FILTER ( ALL ('Table'), 'Table'[index] = PreviousIndex ) ), CALCULATE ( VALUES ( 'Table'[activity_created] ), FILTER ( ALL ('Table'), 'Table'[index] = Index ) ), DAY ) RETURN resultHope this helps!
/Tom
Most Valuable ProfessionalHi Anonymous ,
Is it this you are looking for?
I solved it in two steps with two different calculated columns.
First, we need to create an index column so we can iterate through each row and calculate the difference between their respective dates. Since we have rows which have the same date, we do need to have a second column deciding which one to rank first or second. In your case, I chose to_stage_id, so the ranking looks like this:
index =
VAR MaxToStageID = MAX( 'Table'[to_stage_id] )
VAR result =
RANKX (
ALL ( 'Table' ),
'Table'[activity_created] * MaxToStageID + 'Table'[to_stage_id],
,
ASC,
DENSE
)
RETURN
result
The guys from sqlbi have done a blog about this topic on how to rank / create indexes on multiple columns.
After that, I created another calculated column which uses the index to iterate through the table calculating the date difference:
tomstest =
VAR Index = ('Table'[index])
VAR PreviousIndex = ('Table'[index] - 1 )
VAR result =
DATEDIFF(
CALCULATE (
VALUES ( 'Table'[activity_created] ),
FILTER ( ALL ('Table'), 'Table'[index] = PreviousIndex )
),
CALCULATE (
VALUES ( 'Table'[activity_created] ),
FILTER ( ALL ('Table'), 'Table'[index] = Index )
),
DAY
)
RETURN result
Hope this helps!
/Tom
Helper IVHello there!
I was looking for something similar. But found this.
My question is my interest is to calculate how long the opportunities are staying in each stage. So in your case, when the opportunity entered stage 2, the date was the 14th of May, and when it entered stage 3 the date was the 19th of May. So the duration for stage 2 for this opportunity should be 5. But in your case, it is showing 0 days. That is the result of the previous stage.
Did I get it wrong?
- tackytechtom
Hi Cortana ,
Have a look in here.
There, we cover two different cases on calculating the date difference between rows.. Otherwise, you can just open a new topic, add some sample data and tag me.
I am more than happy to have a look into it! 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/