cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## calculating date difference based on condition

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
1 ACCEPTED SOLUTION
Super User

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.

3 REPLIES 3
Super User

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.

Helper I
Super User

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!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors