Create a Moving Range Measure

[jprlimey]

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

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.

Thanks
J

[EnterpriseDNA]

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 https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

[jprlimey]

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

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

[ankit]

Hi @jprlimey

Incorrect value for “Totals” is the most annoying thing for me with Power BI 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.

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

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

[jprlimey]

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.

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

[powerbi_jp]

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

Calculate A Rolling Average In Power BI Using DAX

[Anonymous48]

Hi, From where do you get the Hrs to Complete column? suddenly you start using 2 similar variables that confuses me. Hrs to complete and Hrs to Complete All
Also do you know if this method works for live datasets? I get a resource exhausted message when trying to calculate the moving range of my data.
thank you

[ystroman]

Hello @Anonymous48, and thank you for adding your question to the Enterprise DNA Forum. The forum post and thread you submitted your question to has not been active for some time. In order to assist with your specific question, please open a new Forum post with your question. Please feel free to reference the legacy posting if that is helpful.

To help the forum members further analyze your current state and visualize your issue, could you please provide as many as you can of:

• Your work-in-progress PBIX file, using sanitized data if necessary
here’s [/u/brianj]@BrianJ’s YouTube video on how to anonymize data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
• Your dataset as an Excel file (again, sanitized if necessary)
• A detailed mock-up (marked-up screenshot of the visual in question or Excel file) showing your desired outcome.

Also, if you provide DAX or Power Query code in your post, please format it using the built-in formatter.