Forum Discussion
Creating a Control Chart
I am trying to create a control chart. My data source is the fields in a semantic model, i.e, I'm not writing SQL. The main line in the chart should show the value for the most current month and the previous six months, so a total of seven months is shown. I need to display a horizontal line showing the average, but the average must be based on the value for the six months prior to the latest month. I can't use the 'Add further analyses to your visual' feature and add the average because that does the average for all 7 months. I do have a DAX measure that I use to determine the average for the previous six months, and I use it in doing standard deviation and it works fine, but when I add this value to the y-axis together with the line showing the monthly values for the 7 months, I would like a straight line but it's not, and the values don't make sense. Once I can get this to work, I then need to add the UCL and LCL.
Please try the measures below:
Latest Month Anchor = CALCULATE ( EOMONTH ( MAX ( 'Dim Date'[Date BK] ), -1 ), ALL ( 'Dim Date' ) ) Avg Value Prior 6 Months = VAR _Anchor = [Latest Month Anchor] RETURN CALCULATE ( AVERAGE ( 'Fact Metrics'[Value] ), DATESINPERIOD ( 'Dim Date'[Date BK], _Anchor, -6, MONTH ), ALL ( 'Dim Date' ) ) Avg Line Flat = VAR _Avg = [Avg Value Prior 6 Months] RETURN IF ( NOT ISBLANK ( [Your Value Measure] ), _Avg )
5 Replies
When you put your average measure on the visual:
- Each month on the X-axis creates a different filter context
- Your measure recalculates for each of those months
- So instead of one constant value → you get a changing line
You need to lock the calculation to the latest month, not the current point on the axis.
Anchor everything to the latest month
Step 1 — Get latest month (independent of axis)
Latest Month =
CALCULATE(
MAX('Date'[Date]),
ALL('Date')
)Step 2 — Average of previous 6 months (anchored)
Avg Prev 6 Months =
VAR LatestMonth = [Latest Month]
RETURN
CALCULATE(
AVERAGE('Fact'[Value]),
DATESINPERIOD(
'Date'[Date],
EOMONTH(LatestMonth, -1),
-6,
MONTH
),
ALL('Date')
)This ensures:
- Always uses the same 6 months
- Ignores the visual axis context
Step 3 — Force it to draw as a flat line
Even with the correct value, Power BI still tries to evaluate per point.
So wrap it:
Avg Line (Flat) =
VAR AvgValue = [Avg Prev 6 Months]
RETURN
IF(
NOT ISBLANK([Value Measure]),
AvgValue
)This Repeats the same value across all visible months and creates a horizontal line
Step 4 — UCL / LCL (Control Limits)
If you're doing a standard control chart:
DAX:
Std Dev Prev 6 Months =
VAR LatestMonth = [Latest Month]
RETURN
CALCULATE(
STDEV.P('Fact'[Value]),
DATESINPERIOD(
'Date'[Date],
EOMONTH(LatestMonth, -1),
-6,
MONTH
),
ALL('Date')
)UCL =
[Avg Prev 6 Months] + 3 * [Std Dev Prev 6 Months]
LCL =
[Avg Prev 6 Months] - 3 * [Std Dev Prev 6 Months]Then apply the same “flat line” trick:
UCL Line =
VAR v = [UCL]
RETURN IF(NOT ISBLANK([Value Measure]), v)
LCL Line =
VAR v = [LCL]
RETURN IF(NOT ISBLANK([Value Measure]), v)Visual:
- Use a Line chart
- Axis → Month
- Values:
- Actual value (7 months)
- Avg Line (Flat)
- UCL Line
- LCL Line
What you say makes perfect sense. I tried it but still get a changing line instead of flat. The measure field I am using comes from the result set of the fact table of the published semantic star schema model for which behind the scenes is SQL. That charts with no problem. I then did what you say for step 1 to derive the latest date in my model and published it, and the value shows the correct date on the dashboard. I then did step 2, but the latest date field does not appear as a field available for me to include in the formula; only measures appear for me to include. So instead I defined it like this, and I even hard coded the ending date to make it simple. The line does come out flat showing one value for every month, but the value is way off, and it shows up for all seven months instead of six months:
Avg Value Prior 6 Months =CALCULATE(AVERAGE('Fact Metrics'[Value]),
DATESINPERIOD('Dim Date'[Date BK],"February 2026",-6,MONTH),ALL('Dim Date'[Date BK]))
Hi ldwf
Average measure is being recalculated for each month in visual, so it doesnot appear as a straight line. For a control chart, average must ignore the month level filter and return a constant value based on the six months prior to the latest month. You can fix this by REMOVEFILTERS in your measure so it evaluates once and stays constant across all seven months then apply same pattern for UCL and LCL.
Please try the measures below:
Latest Month Anchor = CALCULATE ( EOMONTH ( MAX ( 'Dim Date'[Date BK] ), -1 ), ALL ( 'Dim Date' ) ) Avg Value Prior 6 Months = VAR _Anchor = [Latest Month Anchor] RETURN CALCULATE ( AVERAGE ( 'Fact Metrics'[Value] ), DATESINPERIOD ( 'Dim Date'[Date BK], _Anchor, -6, MONTH ), ALL ( 'Dim Date' ) ) Avg Line Flat = VAR _Avg = [Avg Value Prior 6 Months] RETURN IF ( NOT ISBLANK ( [Your Value Measure] ), _Avg )Thank you. This worked for the average line. Although the average line is flat now, I only want it to be visible for the 6 months prior to the current reporting month; it's currently showing for all months, so I don't want it visible for March
