Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to Solution.
@CNENFRNL Figured it out!!
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!!
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |