Forum Discussion

ldwf's avatar
ldwf
Icon for Helper V rankHelper V
5 months ago
Solved

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 th...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    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 )

     

Shravan133's avatar
Shravan133
Icon for Super User rankSuper User
5 months ago

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

 

  • ldwf's avatar
    ldwf
    Icon for Helper V rankHelper V
    5 months ago

    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]))