Timeline visualization - weeks with or without a value

[ReadTheIron]

I'm trying to present a visualization showing both the number of weeks since an asset was repaired and the weeks that the asset failed. Something like this:

(Green = week without failure, Red = week with failure)

I have a file with a list of failure dates, which I've connected via Relationships to the file with the name of the asset and the date repaired (many to one). I'm at a loss where to go from here. I'm thinking a matrix with conditional formatting, but I'm not sure how to bring in the failure date info.

Thank you for any guidance!

[BA_Pete · Accepted solution]

Hi @ReadTheIron ,

You'd need to convert your failures into a number in order to apply them as format conditions.

You could use a measure something like this:

_failureFlag =
IF(
  NOT ISBLANK(yourFailureTable[failureDate]),
  1,
  0
)

Then put this measure into the matrix with your calendar dates as columns.

In the conditional formatting options for the matrix, you would apply formatting as RULES, then set the rules based on this measure to 'value is 1' = RED, 'value is 0 = GREEN'.

Pete

[Anonymous · Accepted solution]

Hi @ReadTheIron ,

I have built a simply data samply as shown below:

Based on my test, I'd suggest you create a new calendar table and build relationship between it and Failure table like this:

Calendar = ADDCOLUMNS( CALENDAR(DATE(2021,1,1),MAX('Repaired Table'[Date Repaired])) ,"Week",WEEKNUM([Date],2))

Then create a flag measure to specify which week is fail based on each asset:

Flag = COUNT('Failure Table'[Date Failed])+0

Create a matrix visual and apply conditional formatting for both background and font:

Turn on Vertical grid and Horizontal grid ,The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ReadTheIron]

Thank you! It took me a bit to figure out how to make this work with my data set (I am still a very new user) but your breakdown was very clear and helpful!

[BA_Pete · Accepted solution]

Hi @ReadTheIron ,

You'd need to convert your failures into a number in order to apply them as format conditions.

You could use a measure something like this:

_failureFlag =
IF(
  NOT ISBLANK(yourFailureTable[failureDate]),
  1,
  0
)

Then put this measure into the matrix with your calendar dates as columns.

In the conditional formatting options for the matrix, you would apply formatting as RULES, then set the rules based on this measure to 'value is 1' = RED, 'value is 0 = GREEN'.

Pete

[BA_Pete]

Hi @ReadTheIron ,

You have kudo'd my answer but not given any details as to whether it worked for you or not.

If it worked for you, can you accept as the solution please so others with the same problem can find the correct answer more quickly and easily.

Thanks,

Pete