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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!