cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate four-shift moving average over hierarchical object

Hi All,

I have the following tables in my data source.

Element

 ElementId ParentElementId ElementLevel ElementName 1 0 1 BB 2 1 2 VS 3 1 2 VD 4 1 2 22 5 2 3 VSCS 6 2 3 VSF 7 3 3 VCS 8 3 3 VF 9 4 3 22CS 10 4 3 22F ... ... ... ...

ElementValueShift

 ElementId Value ShiftTime 2 76 2019-09-28 06:00 2 50 2019-09-28 18:00 2 44 2019-09-29 06:00 2 12 2019-09-29 18:00 2 78 2019-09-30 06:00 2 31 2019-09-30 18:00 3 67 2019-09-28 06:00 3 5 2019-09-28 18:00 3 44 2019-09-29 06:00 3 21 2019-09-29 18:00 3 87 2019-09-30 06:00 3 13 2019-09-30 18:00 ... ... ...

These two tables are linked by ElementId. Within table Element, the child element is linked to the parent element using ElementId-ParentElementId.

What I would like to do is to calculate the four-shift moving average for each element within each level, where the result will be something like this:

 ShiftTime ElementId MovingAverage 2019-09-28 06:00 2 45.5 2019-09-28 18:00 2 46 2019-09-29 06:00 2 41.25 2019-09-28 06:00 3 34.25 2019-09-28 18:00 3 39.25 2019-09-29 06:00 3 41.25 ... ... ...

I tried the following:

MovingAverage =

CALCULATE (
AVERAGE ( [Value] ),
FILTER (
ALLSELECTED ( 'ElementValueShift' ),
RELATED ( 'Element'[ElementLevel] ) = 2
&& 'ElementValueShift'[ShiftTime] >= ( MAX ( 'ElementValueShift'[ShiftTime] ) )
&& 'ElementValueShift'[ShiftTime] < MAX ( 'ElementValueShift'[ShiftTime] ) + 2
)
)
It works if I select a particular element within a level, but if more than one element in the same level are selected, then the moving average is calculated using all the elements in the same level (i.e. they all have the same result).
May I know how can I fix the DAX formula to get the right result?
Solution Sage

Would it simplify things if you gave each element a composite key that included its parents, like "6-2-1" and then had a level column 3.  When you calculate the average it seems like all you care about is the level. To associate an element with a shift/value you just need to identify the precise element, which a composite key would do.

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Announcements

#### Launching new user group features

Learn how to create your own user groups today!