How to ignore first value shown in line graph when using a moving range measure

[pbiuser1122]

Hi, I've encountered an issue when populating a line graph with a moving range calculation. 

The screenshot below shows the graphs that I have.
- The first graph simply shows mean values over time.
- The second graph shows the absolute value of the difference between mean values over time using a Moving Range Measure, I interested the DAX calculation for that below. I am looking for a way to modifiy my DAX calculation for moving range in order to ignore the first data point. I have to calculate the average of the moving range and this first data point is throwing that off. I highlighted in red which data point I am referring to. I also have a date slicer hooked up to my visuals so users can adjust the timeframe, so I need a solution that can cater to that need. 

Here is my DAX calculation for moving range:

I appreciate any help regarding this issue. 

[pbiuser1122]

Hi @v-yilong-msft , thank you for your response.

I tested out your solution, but ran into an issue. I believe this issue occured since my Moving Range field is a measure and not a column or calculated column. 

I slightly modified the DAX measure you provided:

I tested out this measure and it seems that it is removing that first data point, but it outputting the Sum of Mean Value instead of the Moving Range. 

For some additional context, there are filters implemented on the table visual for various fields. One of those filters is on the Created On field to only show a select about of data. Here is what the Created On, Sum of Mean Value, and Moving Range fields look like in a table without any filters:

So, I'm looking for a solution where the first Moving Range value will not be visible no matter what filters are selected. 

[Ahmedx]

pls try this

Measure =  
CALCULATE([Moving Range], 
OFFSET(1,SUMMARIZE(ALLSELECTED('Table'),'Table'[Created On]),
ORDERBY('Table'[Created On])))

[pbiuser1122]

Hi @Ahmedx , thank you for your reply.

I tried your suggested measure. It did not seem to work. When I added this measure to the line graph visual, it seems to be calculating the Sum of Mean Value rather than Moving Range.
Here is the line graph with your suggested Measure:

Which is the same as my Sum of Mean Value line graph:

I tried modifying my Moving Range Measure to incorporate your suggested Measure:

But this did not work, it is still showing that first value:

[Ahmedx]

you don’t need to put everything in one measure
need to be written separately as two measures

[pbiuser1122]

When I try your measure as a separate measure, it does not give the desired output:

The Moving Range line is what my desired output is but with the first value being ignored. 
Your suggested Measure is returning the Sum of Mean Value that the Moving Range Measure is built off of. 

[Ahmedx]

pls try this

if this doesn't help then share the file

Moving Range =
VAR _MinDate =
    MINX ( ALLSELECTED ( 'Data' ), 'Data'[Created On] )
VAR EarlierTime =
    CALCULATE (
        MAX ( 'Data'[Created On] ),
        FILTER (
            ALLSELECTED ( 'Data'[Created On] ),
            'Data'[Created On] < SELECTEDVALUE ( 'Data'[Created On] )
                && 'Data'[Created On] > _MinDate
        )
    )
VAR EarlierMeasureValue =
    CALCULATE ( [Sum of Mean Value], 'Data'[Created On] = EarlierTime )
VAR Moving_Range =
    ABS ( EarlierMeasureValue - [Sum of Mean Value] )
RETURN
    Moving_Range

[pbiuser1122]

I created an example file but do not see an option to attach a pbix file.

[Ahmedx]

upload to onedrive or another

[pbiuser1122]

@Ahmedx Sorry for delayed response, I had to check my companies policies. Unfortunately I cannot share the exact file due to confidentality. However, I created an example file where I pulled a snippet of data from the original file and recreated the issue I am running into. The original file has far more data. 

Here is the a link to download the example file: Example File 

[v-yilong-msft]

Hi, @pbiuser1122 

Based on the information you have provided, Here are my answers to your questions.

1. Based on the image you provided, firstly I created a table.

2. Create a Measure, enter the appropriate DAX code, and remove the date furthest back so that it meets your needs.

Measure = 
  VAR _a = MINX(ALLSELECTED('Table'),'Table'[Created On])
  VAR _b = MAX('Table'[Created On])
RETURN
  IF(_b = _a,BLANK(),MAX('Table'[Moving Range]))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

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

[pbiuser1122]

Hi @v-yilong-msft , thank you for your response.

I tested out your solution, but ran into an issue. I believe this issue occured since my Moving Range field is a measure and not a column or calculated column. 

I slightly modified the DAX measure you provided:

I tested out this measure and it seems that it is removing that first data point, but it outputting the Sum of Mean Value instead of the Moving Range. 

For some additional context, there are filters implemented on the table visual for various fields. One of those filters is on the Created On field to only show a select about of data. Here is what the Created On, Sum of Mean Value, and Moving Range fields look like in a table without any filters:

So, I'm looking for a solution where the first Moving Range value will not be visible no matter what filters are selected.