calculating date difference based on condition

NaveenMD 

result : 

 

in order to achieve this, you need 2 columns helperr  :  index and path. 

 

in power query create the undex using  : addcolumns --> add index -- . start index from 1 .

 

 

in power bi, create path column as follow : 

path = 

var latest_green = 
SELECTCOLUMNS(
    INDEX(
        1,
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(
                Table4 , Table4[Date] <= EARLIER( Table4[Date]) && Table4[color code] = "green"
            ),
            [Date], [Index] 
            )
        )
        ,ORDERBY([Index] , DESC ) 
    ),
    [Date]
)


var ds = 
FILTER(
    Table4 , Table4[Date] < EARLIER( Table4[Date]) && Table4[Date] >=latest_green
)


var ds_colors = SELECTCOLUMNS(ds , [color code] ) 

RETURN
CONCATENATEX(ds_colors , [color code] , "|")

 

 

 

then create the column 2  as follow : 

Column 2 = 

var currentcolor =  Table4[color code]
var currentdate = Table4[Date]
var currentindex =  Table4[Index]
var prev_color =  SELECTCOLUMNS(FILTER(Table4,Table4[Index] = currentindex -1 ) , [color code] ) 

var currentpath =  Table4[path]
var path_contains_green = CONTAINSSTRING( currentpath,"green" ) 
var path_contains_yellow = CONTAINSSTRING(currentpath,"yellow"  ) 
var path_contains_red = CONTAINSSTRING(currentpath,"red"  ) 


var res = 
SWITCH(
    TRUE(), 
    currentpath = BLANK() , blank() , 
    currentcolor = prev_color , blank() , 
    currentcolor = "red" && path_contains_red ,
     SWITCH(
        TRUE(),
        prev_color ="red" , BLANK() , 
        var prev_date =  SELECTCOLUMNS(FILTER(Table4,Table4[Index] = currentindex -1 ) , [Date] ) 
        return INT(currentdate - prev_date)
     ),




    currentcolor = "red" && path_contains_green , 
    var ds_green = 
    FILTER(
        Table4,
        Table4[Date]<currentdate && Table4[color code] = "green"
    ) 
    var green_date = SELECTCOLUMNS(INDEX(1,distinct(SELECTCOLUMNS(ds_green ,[Date], [Index])),ORDERBY([Index] , DESC ) ) ,[Date])
    return  INT( currentdate - green_date ) ,




    currentcolor = "red" && path_contains_yellow ,
    var ds_yellow = 
    FILTER(
        Table4,
        Table4[Date]<currentdate && Table4[color code] = "yellow"
    ) 
    var yellow_date = SELECTCOLUMNS(INDEX(1,distinct(SELECTCOLUMNS(ds_yellow ,[Date], [Index])),ORDERBY([Index] , DESC ) ) ,[Date])
    return  INT( currentdate - yellow_date ) ,



    currentcolor = "yellow" && path_contains_red , blank(), 




    currentcolor = "yellow" && path_contains_green , 
    var ds_green = 
    FILTER(
        Table4,
        Table4[Date]<currentdate && Table4[color code] = "green"
    ) 
    var green_date = SELECTCOLUMNS(INDEX(1,distinct(SELECTCOLUMNS(ds_green ,[Date], [Index])),ORDERBY([Index] , DESC ) ) ,[Date])
    return  INT( currentdate - green_date )







)

return res

 

 

 

let me know if this works.