Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |