cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kuehcj
Frequent Visitor

Calculate four-shift moving average over hierarchical object

Hi All,

I have the following tables in my data source. 

Element

ElementIdParentElementIdElementLevelElementName
101BB
212VS
312VD
41222
523VSCS
623VSF
733VCS
833VF
94322CS
104322F
............

ElementValueShift

ElementIdValueShiftTime
2762019-09-28 06:00
2502019-09-28 18:00
2442019-09-29 06:00
2122019-09-29 18:00
2782019-09-30 06:00
2312019-09-30 18:00
3672019-09-28 06:00
352019-09-28 18:00
3442019-09-29 06:00
3212019-09-29 18:00
3872019-09-30 06:00
3132019-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:

ShiftTimeElementIdMovingAverage
2019-09-28 06:00245.5
2019-09-28 18:00246
2019-09-29 06:00241.25
2019-09-28 06:00334.25
2019-09-28 18:00339.25
2019-09-29 06:00341.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?
1 REPLY 1
kentyler
Solution Sage
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


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.