calculating date difference based on condition

[NaveenMD]

Using the data wated to calculate how many days does it take the system to become green to yellow, green to red and yellow to red incase there are duplicate reds then then the calculation should be till first red.

Basicall I am trying to gage the life of the system where red is considered to be full damaged and yellow to be semi damage.

need to calculate the time taken fron green ie. good contion to semi-damage and  good condition to full damage.

the numerical values should refelect in the first red or yellow in the cycle 

Date	temp	color code
29.11.2017	222	green
04.12.2017	271	yellow
21.12.2017	290	red
10.01.2018	293	red
29.01.2018	263	yellow
06.02.2018	315	red
19.02.2018	269	yellow
24.03.2018	239	green
06.04.2018	269	yellow
03.07.2018	275	yellow
03.08.2018	299	red
22.08.2018	295	red

to this as a solution

Date	temp	color code	Value	Formula
29/11/2017	222	green
4/12/2017	271	yellow	6	A2-A3
21/12/2027	290	red	22	A2-A4
10/1/2018	293	red
29-01-2018	263	yellow
6/2/2018	315	red	9	A6-A7
19-02-2018	269	yellow
24-03-2018	239	green
6/4/2018	269	yellow	13	A9-A10
3/7/2018	275	yellow
3/8/2018	299	red	132	A9-A12
22-08-2018	295	red	NA
23-10-2018	265	yellow	NA
23-11-2018	286	yellow	NA
27-12-2018	261	yellow	NA
6/2/2019	267	yellow	NA
23-02-2019	253	yellow	NA
2/3/2019	255	yellow	NA

https://docs.google.com/spreadsheets/d/19o9_gqVy_JdroxtRqxGndyzvaQOpHqlbHAz2RVsKF1I/edit?usp=sharing
@amitchandak  @tamerj1 

[Daniel29195 · Accepted solution]

@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. 

[NaveenMD]

@Ritaf1983  @lbendlin @foodd @rajendraongole1 

[rajendraongole1]

Hi @NaveenMD - Create a calculated column to calculates the number of days it takes for the system to change from green to yellow, green to red, and yellow to red. 

TransitionDays =
VAR CurrentDate = 'Table'[Date]
VAR CurrentColor = 'Table'[color code]
VAR PrevGreenDate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
'Table'[Date] < CurrentDate &&
'Table'[color code] = "green"
)
)
VAR PrevYellowDate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
'Table'[Date] < CurrentDate &&
'Table'[color code] = "yellow"
)
)
VAR GreenToYellow =
IF(
CurrentColor = "yellow" && NOT ISBLANK(PrevGreenDate),
DATEDIFF(PrevGreenDate, CurrentDate, DAY),
BLANK()
)
VAR GreenToRed =
IF(
CurrentColor = "red" && NOT ISBLANK(PrevGreenDate),
DATEDIFF(PrevGreenDate, CurrentDate, DAY),
BLANK()
)
VAR YellowToRed =
IF(
CurrentColor = "red" && NOT ISBLANK(PrevYellowDate),
DATEDIFF(PrevYellowDate, CurrentDate, DAY),
BLANK()
)
RETURN
IF(
CurrentColor = "yellow",
GreenToYellow,
IF(
CurrentColor = "red" && NOT ISBLANK(GreenToRed),
GreenToRed,
YellowToRed
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!