sum of items more than 200 for 2 consecutive working days in a month- how to highlight

[deeavhad]

As per below screenshot I want to highlight sum of items more than 200 on given date for 2 consecutive working days in a month, please help

[stevejoyce]

Try below.  Update Sum( [items] ) with your actual measure expression and [date_field] by your x-axis date dimension field name.

If(Sum( [items] ) >200 and above(Sum({<[date_field]=>} [items] ),1) > 200, red())

[deeavhad]

hi Thanks for you reply, its working but somehow it catching values below 200 , please see below

Dimention "= UKI_ES_Opened.autoCalendar.Date"

Measure "= sum({$<[UKI_ES_Opened.autoCalendar.Year] = {'$(=year(today()))'},[UKI_ES_Opened.autoCalendar.Month] = {'$(=month(Today()))'}
,[SLA] = {'24','48','72'}>}UKI_ES_Items) 

Color Expression=If(Sum( [UKI_ES_Items] ) >=200 and above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=>} [UKI_ES_Items] ),-1) >= 200, 'Red')

can you see 2nd bar, also I want Friday and Monday should be considered as consecutive

[stevejoyce]

Likely it's the SLA set analysis that's not in it.  I would use the entire measure's set anslysis in your color expression, but you can at least try this:

=If(Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] ) >=200 and above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),-1) >= 200, 'Red')

[deeavhad]

I have used .......=If(Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] ) >=200 and above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),-1) >= 200, 'Red')........this in color expression but now it skipping bars which are consecutively more than 200

[stevejoyce]

Oh sorry i put -1 in my above offset.  That should be 1 to look at previous record, right now it's looking at next date...

=If(Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] ) >=200 and above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),1) >= 200, 'Red')

[deeavhad]

Now after puting '1' instead of '-1' is showing as below, skipping bar of 6th and 13th sep.

Also I tried removing 1 but no change

[stevejoyce · Accepted solution]

Ok i understood miscorrectly.  So if the first day is to be included in the highlighting of a 2-day in a row then this:

=If(Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] ) >=200

and (

above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),1) >= 200

or

above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),-1) >= 200

)

, 'Red')

[deeavhad]

Thanks, But still it skipping last bar which is above 200 and has previous consecutive bar as 200+

[stevejoyce]

It should work.  Add those 3 expressions (current date measure, previous day, next day) in a straight table and make sure they are matching the graph.  I expect it's off and is because of a different in the set analysis.  The set analysis should be consistent between them.