Forum Discussion
ldwf
Helper V
Helper VCreating 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...
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 )
cengizhanarslan
Super User
Super UserPlease 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 )
ldwfHelper V
Helper VThank 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