Identify if a value is above average for a number of consecutive days

[JarradMurray]

Hi All,

I've gotten stuck trying to identify if the total value of a day is above the overall average value across a couple of rules.

Rule 1: identify is a daily value, on any of the last 5 days, is above average.

Rule 2: identify is a daily value is above average for 5 consecutive days, over the last 14 day period.

What I am trying to achieve is flag where these rules have been triggered, using a set of icons so I only need a yes/no, 1/0 outcome.

I've attached an example file of my data to try and show what I'm working with.

Example PBI File 

Any help you can offer is greatly appreciated.

[123abc]

In Power BI, you can achieve this by creating calculated columns or measures to identify if a daily value is above average based on your specified rules. You'll need to use DAX (Data Analysis Expressions) functions to create these calculations. Here's a step-by-step guide on how to implement Rule 1 and Rule 2:

Rule 1: Identify if a daily value, on any of the last 5 days, is above average:

1. First, you need to calculate the average of the daily values for each day over the last 5 days. You can do this using the AVERAGEX function.

DailyAvgLast5Days = AVERAGEX(FILTER(ALL(Dates), Dates[Date] >= EARLIER(Dates[Date]) - 4 && Dates[Date] <= EARLIER(Dates[Date])), [Daily Value])

1. This formula calculates the average of daily values for each day over the last 5 days.

2. Next, create a calculated column or measure to check if the daily value is above the daily average for the last 5 days.

AboveAvgLast5Days = IF([Daily Value] > [DailyAvgLast5Days], 1, 0)

1. This formula will return 1 if the daily value is above the average of the last 5 days, otherwise, it will return 0.

Rule 2: Identify if a daily value is above average for 5 consecutive days, over the last 14-day period:

1. Calculate the rolling average of daily values for each day over the last 14 days. You can use the AVERAGEX function again, but this time, it should consider the previous 14 days.

RollingAvgLast14Days =
VAR CurrentDate = Dates[Date]
RETURN
AVERAGEX(FILTER(ALL(Dates), Dates[Date] >= CurrentDate - 13 && Dates[Date] <= CurrentDate), [Daily Value])

1. This formula calculates the rolling average of daily values over the last 14 days for each day.

2. Create a calculated column or measure to check if the daily value is above the rolling average for 5 consecutive days.

AboveAvgConsecutive5Days =
IF(
COUNTROWS(FILTER(ALL(Dates), [AboveAvgLast5Days] = 1)) >= 5, 1, 0
)

1. This formula checks if there are at least 5 consecutive days where AboveAvgLast5Days is 1, and if so, it returns 1; otherwise, it returns 0.

Now, you can use these calculated columns or measures in your visuals and set up conditional formatting or icons to display the results. For example, you can use the AboveAvgLast5Days and AboveAvgConsecutive5Days columns to determine if the rules are triggered and then use conditional formatting to display icons (e.g., green checkmark for 1, red cross for 0) to represent the outcomes.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[JarradMurray]

Many thanks for coming back to me on this @123abc , unfortunately your solution doesn't quite meet what I need to achieve (sorry, I probably didn't explain it well enough).

In my data I am calculating an average value for the duration of the current financial year which alters based on selections made within a set of slicers.

What I am trying to identify for rule 1 is if any of the last 5 days worth of values are above this average.

e.g.

AVG = 26

Day	Mon	Tue	Wed	Thu	Fri
Value	22	24	30	25	19

 Because the value for Wednesday is above the average the rule would flag and indicate that it has been triggered.

Similarly Rule 2 is looking where a days value is above the average but 5 days in a row.

e.g.

AVG = 26

Day	Mon	Tue	Wed	Thur	Fri	Sat	Sun	Mon	Tue
Value	28	30	27	31	23	27	12	24	21

This wouldn't trigger as there are 5 values above the average but they aren't over 5 consecutive days.

Day	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
Value	17	30	27	31	29	27	12	24	21

This would trigger the rule as the Tue - Sat values are all over the average.

For rule 1 I thought of a measure that checks each day over the last 5 and if one value is above the average, flag it.  I'm not sure how to step through each day in DAX so this is where I am stuck.

For rule 2, I though a similar idea as rule 1 but I have no idea how to get DAX to identify if the rule is triggered 5 times in succession.

[123abc · Accepted solution]

Thank you for the clarification. You can achieve the rules you described with slightly different DAX measures. Let's revise the approach to match your requirements:

Rule 1: Identify if any of the last 5 days' values are above the current financial year's average.

Assuming you have a measure that calculates the current financial year's average, you can create a new measure to check if any of the last 5 days' values are above this average. Let's call this measure "Rule 1":

Rule 1 =
VAR CurrentDate = MAX('YourDateColumn')
VAR Last5Dates = FILTER(ALL('YourDateColumn'), 'YourDateColumn' >= CurrentDate - 4 && 'YourDateColumn' <= CurrentDate)
VAR AverageFY = [YourFinancialYearAverageMeasure]
VAR AboveAverageDays = COUNTROWS(FILTER(Last5Dates, [YourValueColumn] > AverageFY))
RETURN
IF(AboveAverageDays > 0, 1, 0)

This measure checks if any of the last 5 days' values are above the average for the current financial year. If yes, it returns 1; otherwise, it returns 0.

Rule 2: Identify if there are 5 consecutive days where the value is above the current financial year's average.

For this rule, you can create another measure to check if there are 5 consecutive days where the daily value is above the financial year's average. Let's call this measure "Rule 2":

Rule 2 =
VAR CurrentDate = MAX('YourDateColumn')
VAR Last14Dates = FILTER(ALL('YourDateColumn'), 'YourDateColumn' >= CurrentDate - 13 && 'YourDateColumn' <= CurrentDate)
VAR AverageFY = [YourFinancialYearAverageMeasure]
VAR ConsecutiveAboveAverage = COUNTROWS(FILTER(Last14Dates, [YourValueColumn] > AverageFY))
RETURN
IF(ConsecutiveAboveAverage >= 5, 1, 0)

This measure counts the days in the last 14 days where the daily value is above the financial year's average and checks if there are at least 5 consecutive days meeting this condition. If yes, it returns 1; otherwise, it returns 0.

These measures should help you identify when the rules are triggered based on your specific requirements. You can use these measures in your Power BI report to visualize the outcomes as icons or any other visual representation you prefer.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[shop-tinh-yeu]

Thank you

[JarradMurray]

This is fantastic.

Thank you very much @123abc, this works perfectly.

[123abc]

You are always welcom sir.