Calculate four-shift moving average over hierarchical object

[kuehcj]

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?

[kentyler]

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.