Days spent in stage

[harshadrokade]

Hi sir/mam,

I have below data and I want to generate 'Days spent on the Oldstage' column in power bi. I have also given referance column to know how I calculated this column in excel. Basically I want to know how many days were spent at a stage befor moving to a new stage. I want to follow the sequence of the stages as A>B>C>D.

Id	Movement start Date	OldStage	NewStage	Days spent on the Oldstage	Formula for referance
1	28-10-20 15:32	B	C	7.587199074	C date - B date
1	28-10-20 15:29	A	B	0.001886574	B date - A date
1	05-11-20 5:38	C	D	3	D date - C date
1	08-11-20 5:38	D	E	NA	NA
2	28-11-20 15:29	A	B	1	B date - A date
2	29-11-20 15:29	B	C	10	C date - B date
2	09-12-20 15:29	C	D	2	D date - C date
2	11-12-20 15:29	D	E	NA	NA

[Fowmy]

@harshadrokade 

Create the following calculated column in your table:?

Days Spent = 

VAR __FromStage = Stages[OldStage]
VAR __TOStage = Stages[NewStage]
VAR __FromTime = 
    CALCULATE(
        MAX( Stages[Movement start Date] ) , 
        Stages[OldStage] = __TOStage,
        ALLEXCEPT( Stages , Stages[Id] )
    )
VAR __Result= 
         DATEDIFF( Stages[Movement start Date] , __FromTime , DAY ) 
RETURN
    __Result
        

[danextian]

Hi @harshadrokade ,

For row 2, 8-10-20 15:29 + 0.001886574 = 28-10-2020 15:31 but this  datetime is not anywhere in your sample data.  And is this by Id?

[harshadrokade]

Thanks @danextian for looking into this.

I have this datetime, its actually 28-10-20 15:32.Ignore the last decimaldue to rounding off..

It is the start day/time of Stage B. When I compare this date with start day/time of Stage A (28-10-20 15:29), I get value as 0.001886574.

[danextian]

Hi @harshadrokade 

Days spent = 
CALCULATE (
    MAX ( 'Table'[Movement start Date] ),
    FILTER (
       'Table', 
        'Table'[OldStage] = EARLIER ( 'Table'[NewStage] ) && 'Table'[Id] = EARLIER( 'Table'[Id])
    )
) - 'Table'[Movement start Date]