Need a DAX measure to calculate the AVG over 3 of 4 prior weeks

[Anonymous]

I am building a Power BI Report that has a Filter on All Pages for 'The Last 4 Weeks'.

I need to write a measure that will calculate the average agent count of the prior 3 weeks, so that I can compare that AVG to the prior last completed week agent count.

EXAMPLE:

Week Ending Date	Agent Count
8/30/2020	15
9/6/2020	10
9/13/2020	15
9/20/2020	18

AVG of 8/30/2020, 9/6/2020, 9/13/2020 = 13  (prior 3 weeks)

9/20/2020 - prior last completed week = 18

**I cannot use the specific dates in the table, as next week the dates will change.

Example of how the dates and count will change each week

Week Ending Date	Agent Count
9/6/2020	10
9/13/2020	15
9/20/2020	18
9/27/2020	15

AVG of 9/6, 9/13, 9/20 = 14

Does anyone have any knowledge on how to write this measure? (Agent Count is coming From Table.A and Week Ending Date is coming from Table.B)

[CNENFRNL]

Hi, @Anonymous , pls try this measure

Avg prior 3wk = 
VAR __latest_wk = MAX ( 'Table'[Week Ending Date] )
RETURN 
    AVERAGEX (
        DATESINPERIOD ( 'Table'[Week Ending Date], __latest_wk -1, -21, DAY ),
        CALCULATE( MAX ( 'Table'[Agent Count] ) )
    )

[Anonymous]

Hi @CNENFRNL 

Thanks so much for sending along that measure!

It worked with my dummy data table (that I used in the post), but when I applied it to my Actaul tables I am getting a different result than expected.

In my actauls, Agent Count has Summarization Sum as it's properties, could this be affecting the outcome? and If so, how can I alter the measure to take that into account?

[Anonymous · Accepted solution]

@CNENFRNL  Figured it out!!

Avg prior 3wk =

VAR __latest_wk = MAX ( 'Table'[Week End Date] )

RETURN

AVERAGEX

(

DATESINPERIOD (  'Table'[Week End Date], __latest_wk -1, -21, DAY ),

CALCULATE( MAXX ( VALUES (  'Table'[Active Agents]  ), SUM ( 'Table'[Active Agents] ) ) )

)