Shift median based on last consecutive values

[MatthewMcilorum]

Hi all,

This is my first post but I have been using this community to find many answers to questions i've had in the past as i've learned powerBI.

I can calculate a moving median like 7 day rolling but I am struggling to work out a measure that will recalculate the median from that data point if the previous consecutive points are all higher or lower than the current median.

For example the median would be calculated using the first 8 data points and carry on. at somepoint the previous 8 data points (from today's value) might have been higher than the median and therefore I want to median to be recalculated using those 8 points and continue on with the new median.

It is a similar concept to shifts or runs in SPC charts but I wuold like the median to also change. Is this possible in PowerBI currently?

thanks,
Matthew

[Anonymous]

Hi @MatthewMcilorum ,

Please try these two measures.

Measure = CALCULATE(MEDIAN('Table'[Value]),DATESINPERIOD('Table'[Date],MAX('Table'[Date]),-8,DAY))

Seven-day median = 
VAR _cd =
    MAX ( 'Table'[Date] )
VAR _pd =
    MAX ( 'Table'[Date] ) - 1
VAR _cv =
    MAX ( 'Table'[Value] )
VAR _pv =
    CALCULATE ( [Measure], 'Table'[Date] = _pd )
VAR _value =
    IF ( _pv > _cv, _pv, _cv )
VAR _table =
    ADDCOLUMNS (
        FILTER ( 'Table', 'Table'[Date] <= _cd && 'Table'[Date] >= _cd - 7 ),
        "value2", _value
    )
VAR _mid =
    MEDIANX ( _table, _value )
RETURN
    _mid

Attached PBIX file for reference.

Feel free to let me know if I misunderstood your needs. It would be useful to give an example of the calculation process and the expected output.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[MatthewMcilorum]

Hi Gao,

thanks for having a go at this, unfortuantly this is not quite what I was after. My desired outcome would be a flat median line that would shift up of down if the previous 8 values were above or below the median line and would produce something like in the picture below (hopefully this helps)