Forum Discussion

ldwf's avatar
ldwf
Icon for Helper V rankHelper V
6 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 )

     

krishnakanth240's avatar
krishnakanth240
Icon for Super User rankSuper User
6 months ago

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.