Forum Discussion

MatthewMcilorum's avatar
MatthewMcilorum
Regular Visitor
4 years ago

Shift median based on last consecutive values

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

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      MatthewMcilorum
      Regular Visitor
      4 years ago

      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)