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.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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.