If value same for 3 months in any rolling 6 month period

[shobhagollapudi]

Hi I have a query where I am tying to compare and assign RAG status for any 3 months values in a rolling 6 months period. For example If the % is less than 75% then assign RED etc.

Example data:

Date	Value	Target %
01/10/2023	6027	50.23%
01/11/2023	8950	74.58%
01/12/2023	8500	70.83%
01/01/2024	11500	95.83%
01/02/2024	4931	41.09%
01/03/2024	5629	46.91%

[ryan_mayu · Accepted solution]

do you mean mark the number in red?

i think conditional formatting can work for that.

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting?wt.mc...

[shobhagollapudi]

@ryan_mayu  Thanks for your response. I understand the conditional format but what the query should look for if the value is less than 75% for any 3 months of the rolling 6 months then apply conditional format

[ryan_mayu · Accepted solution]

@shobhagollapudi 

Here is a workaround for you, you can create columns to find out the percentage that you need to mark.

Column =

var _start=maxx(FILTER('Table','Table'[Date]=EDATE(EARLIER('Table'[Date]),-5)),'Table'[Date])

var _count=CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])&&'Table'[Date]>=_start&&'Table'[Target %]<0.75))

return if(ISBLANK(_start),BLANK(),if(_count>2,1))

Column 2 =

VAR _end=maxx(FILTER('Table','Table'[Date]=EDATE(EARLIER('Table'[Date]),5)),'Table'[Date])

VAR _max=max('Table'[Date])

return if(ISBLANK(_end)&& MAXX(FILTER('Table','Table'[Date]>=EARLIER('Table'[Date])&&'Table'[Date]<=_max&&'Table'[Column]=1),'Table'[Column])=1 && 'Table'[Target %]<0.75,"RED",if(MAXX(FILTER('Table','Table'[Date]>=EARLIER('Table'[Date])&&'Table'[Date]<=_end&&'Table'[Column]=1),'Table'[Column])=1 && 'Table'[Target %]<0.75,"RED"))

[ryan_mayu · Accepted solution]

do you mean mark the number in red?

i think conditional formatting can work for that.

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting?wt.mc...