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

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors