Moving Range to be calculated at different hierachies

[helen_p]

Dear Power BI user

I have set up a table in power BI which looks similar to the example table below:

Week Commencing     Count Of Additions      Care Group    Clinician  Moving Range

01/01/2020                                  3                          A                   1                 

07/01/2020                                  6                          A                   1               3

14/01/2020                                  4                          A                   1               2

01/01/2020                                  9                          B                    2              5

07/01/2020                                  8                          B                    2

14/01/2020                                  7                          B                    2

01/01/2020                                  9                          A                    3

07/01/2020                                  8                          A                    3

14/01/2020                                  7                          A                    3

I initially created 2 new index columns  called "Index" starting from 0 and and Index.1" starting from 1. 

The calculation below only works at the most granular level when looking at clinician.  I think it only works at clinician level as the index fields are always looking at the row above to calculate the difference in count of wl additions each row

I.e. 

Moving Range = ABS('Waiting List Extract'[Count Of WL Additions]-(CALCULATE(sum('Waiting List Extract'[Count Of WL Additions]),FILTER('Waiting List Extract','Waiting List Extract'[Index.1]=EARLIER('Waiting List Extract'[Index])))))

I was wondering if someone could help me understand how I can get my calculation to work with the index columns so that the moving range works at different levels of hiercahy in the table I.e. works out the moving range at  Care Group Level

Kind Regards

Helen

[MFelix]

Hi @helen_p  no need to do the INDEX you just need to add the following measure:

Moving_Average = 
VAR temp_table =
    FILTER (
        ALL ( 'Waiting List Extract'[Week Commencing] );
        'Waiting List Extract'[Week Commencing]
            < SELECTEDVALUE ( 'Waiting List Extract'[Week Commencing] )
    )
VAR Previous_Date =
    MAXX ( temp_table; 'Waiting List Extract'[Week Commencing] )
RETURN
    ABS (
        SUM ( 'Waiting List Extract'[Count of Addtions] )
            - CALCULATE (
                SUM ( 'Waiting List Extract'[Count of Addtions] );
                FILTER (
                    ALL (
                        'Waiting List Extract'[Care Group];
                        'Waiting List Extract'[Clinician];
                        'Waiting List Extract'[Week Commencing];
                        'Waiting List Extract'[Count of Addtions]
                    );
                    'Waiting List Extract'[Week Commencing] = Previous_Date
                        && 'Waiting List Extract'[Care Group]
                            IN VALUES ( 'Waiting List Extract'[Care Group] )
                                && 'Waiting List Extract'[Clinician]
                                    IN VALUES ( 'Waiting List Extract'[Clinician] )
                )
            )
    )

This will pickup the previuos week date and then based on the aggregation make the calculation:

Check PBIX file attach.