Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hdavies_d
Frequent Visitor

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

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)

1 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] ) ) )
)

View solution in original post

3 REPLIES 3
CNENFRNL
Community Champion
Community Champion

Hi, @hdavies_d , 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] ) )
    )

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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?

@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] ) ) )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors