Create a Moving Range Measure

I have the need to create a moving range measure using Dax.

I have slicers that enable the user to select various categories, which enables ultimately a very dynamic chart output.

The Table below shows the output (based on slicer selections) a number of Review No’s, added to this is a measure named Hrs to Complete All

Table Review

I then created a measure to calculate the moving range of the Hrs to Complete All measure

mR =

VAR EarlierReviewNo =
CALCULATE(
MAX( ‘Contract Review’[Review No]),
FILTER(
ALLSELECTED(‘Contract Review’[Review No]),
‘Contract Review’[Review No] < SELECTEDVALUE (‘Contract Review’[Review No])
)
)

VAR EarlierMeasureValue =
CALCULATE( SUM ( ‘Contract Review’[Hrs to complete]), ‘Contract Review’[Review No] = EarlierReviewNo )
RETURN

ABS ( EarlierMeasureValue - SUM (‘Contract Review’[Hrs to complete]))

After applying the mR measure, the table now shows records that are not associated to the Slicer selections. How do I modify the measure such that the mR is calculated on the slicer selections only. I suspect the > FILTER(

        ALLSELECTED('Contract Review'[Review No]),
        'Contract Review'[Review No] < SELECTEDVALUE ('Contract Review'[Review No]) is the culprit.

Image below shows the output once the mR measure is applied to the table.

Table Review 2

Thanks
J

Hi @jprlimey, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

I am getting closer to a final solution with some more research.

I modified the measure to include if IF statement to check when Hrs to Complete is blank, below is new measure. This cleaned up the table so that Review Numbers that are out of context are not shown. in the table.

test 3 mR = 

VAR EarlierReviewNo =
    CALCULATE(
        MAX( 'Contract Review'[Review No]),
        FILTER(
            //'Contract Review', 
            ALLSELECTED('Contract Review'[Review No]),
            'Contract Review'[Review No] < SELECTEDVALUE ('Contract Review'[Review No])
        )
    )

VAR EarlierMeasureValue = 
    CALCULATE( SUM ( 'Contract Review'[Hrs to complete]), 'Contract Review'[Review No] = EarlierReviewNo )
RETURN

IF( [Hrs to Complete All] = BLANK(),
    BLANK(),
ABS( EarlierMeasureValue - SUM ('Contract Review'[Hrs to complete])))

Below is the table output, as you can see the context is now correct (no blank Hrs to Complete numbers).
Table Rewiew-3

I’m encouraged to get to this point, however… the Total is incorrect for the new measure test 3 mR. The actual total should be 76.144. Ironically the total shown is the exact same total shown in the table image when I initiated this post.

I check some blogs and video that Sam had created for virtual tables, as this appears to be one solution. I played around but couldn’t get it to work. It looks like I would need to create a Virtual table with the same structure as the measure (with the Variables and Filtering). Not sure where I go from here!

BTW I do have one other slight issue, for control charting purposes, such as an I-MR chart, I need to exclude the 1st record in the test 3 mR measure, it should actually be blank. How can this record be made blank (Review No 23601 shows as 0.032 for test 3 mR measure it should be blank)?

Thanks
J

Hi @jprlimey

Incorrect value for “Totals” is the most annoying thing for me with Power BI :frowning: Why can’t they provide an option to just sum up all the records for that measure in visual.

Anyways based on your formula, problem is for “Total”, all Review No’s are available and as you are using SelectedValue function it will return Blank(), thus doing Sum over all the Reveiw No.There is no single function that can help.

As you rightly mentioned you need to create Virtual table in calculation and along with HASONEVALUE() or ISINSCOPE(). Refer to below article, it has got lots of details.

Regarding your second problem, try below. Replace MonthnYear with ReviewNo

MinValue =
VAR MinRec =
    CALCULATE ( MIN ( Dates[MonthnYear] ), ALLSELECTED ( Dates[MonthnYear] ) )
RETURN
    IF ( SELECTEDVALUE ( Dates[MonthnYear] ) = MinRec, BLANK (), [Total Sales] )

Please share PBIX file if you want detailed answer on 1st Problem.

Thanks
Ankit J

Thank you all for your assistance, I’m slowly getting to a resolution on this issue. I have fixed the correct Totals issue

The Card Image below, shows that the test 14 mR measure shows as 76.159, really close to the required number of 76.112

The DAX below is how I solved it.

test 14 mR = 

SUMX(
    FILTER(
        SUMMARIZE('Contract Review','Contract Review'[Review No]),
        [test 3 mR] > 0),
        [test 3 mR])

You don’t want to know how long I messes around with this today, I’ll be claiming overtime for sure! This simple solution came to me after watching on of Sam’s video’s

Iterating Logic Through Virtual Tables - Advanced DAX Concepts In Power BI

Unfortunately I still need to resolve the first record issue. Shown below in the Table image. the first value shown as 0.032 under the column test 3 mR should actually be blank.

Table Rewiew-4

I did play around with the suggestion from Ankit J, here is the Dax measure, it did not work

test 10 mR = 

VAR MinRec =
    CALCULATE ( MIN ( 'Contract Review'[Review No] ), ALLSELECTED ( 'Contract Review'[Review No] ) )
RETURN
    IF ( SELECTEDVALUE ( 'Contract Review'[Review No] ) = MinRec, BLANK (), [test 3 mR] )

Thnaks
J

Also, just going to leave this here in case someone needs it…

1 Like