Forum Discussion

cbruhn42's avatar
cbruhn42
Icon for Helper III rankHelper III
2 years ago
Solved

Averaging Last 4 values using an index

I am trying to figure out how to average the last 4 values for a given attribute using an index that I setup by first sorting by the attribute.  So when I filter down to a specific attribute the inde...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi cbruhn42 ,

    I think to use the column 'SampleDatetime'.

    Measure = 
VAR _a = SELECTEDVALUE('Table'[SampleDateTime])
VAR _b = DATE(YEAR(_a),MONTH(_a),DAY(_a))
VAR _startTime = _b + TIME(0,0,0)
VAR _endTime = _b + TIME(23,59,59)
VAR _endIndex =
    CALCULATE (
        MAX ( 'Table'[Attribute Index] ),
        ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] ),
        'Table'[SampleDateTime] >= _startTime && 'Table'[SampleDateTime] <= _endTime
    )
VAR _sample =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] )
    ) //Calculate sample size
VAR _startIndex =
    IF ( _sample < 4, _endIndex - _sample + 1, _endIndex - 3 )
RETURN
    CALCULATE (
        AVERAGEX (
            FILTER (
                'Table',
                'Table'[Attribute Index] >= _startIndex
                    && 'Table'[Attribute Index] <= _endIndex
            ),
            [Value]
        ),
        ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] )
    )

     

cbruhn42's avatar
cbruhn42
Icon for Helper III rankHelper III
2 years ago

Forgot to add that I need to be able to account for if for some reason there aren't 4 samples taken for a given attribute.  Like if one of the 4 samples didn't process correctly and we only have 3 for that set I would just want to average those 3.  So I need to be able to reference the index value and then check against the valve number to make sure it matches the previous valve number I think.