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.
Hi All,
I'm trying to create a measure to get Previous 3Mos average, yet could not figure out on how to do this in DAX. I need to get the previous 3 months average of completed pct infront of the current month dynamically. Thank you.
Sample : previous 3 month average
1/1/2019 shows no average result
2/1/2019 shows no average result
3/1/2019 shows no average result
4/1/2019 shows average of completd pct of (Jan-Feb-Mar)
5/1/2019 shows average of completd pct of (Feb-Mar-Apr)
and so on
Sample Data and expected result:
Thank you. in Advance.
Is there anyone has an idea to do this in DAX? Thank you.
Hi @JWick1969
try a measure
Measure =
var _AVGpct = CALCULATE(AVERAGE(Query[Completed pct]);FILTER(ALL(Query);datediff(Query[Month];SELECTEDVALUE(Query[Month]);MONTH)<=3 && Query[Month]< SELECTEDVALUE(Query[Month])))
var _AVGcount = CALCULATE(COUNTROWS(Query);FILTER(ALL(Query);datediff(Query[Month];SELECTEDVALUE(Query[Month]);MONTH)<=3 && Query[Month]< SELECTEDVALUE(Query[Month])))
RETURN
IF(_AVGcount>=3;_AVGpct;BLANK())
do not hesitate to give a kudo to useful posts and mark solutions as solution
Thanks for the reply. I got an error which i think the semicolon. what is the use of semicolon? is this a parameter separator? by the way, my PBI version (April2019)
Hi @JWick1969
it's a system localization question.
replace all ";" in my statement to ","
do not hesitate to give a kudo to useful posts and mark solutions as solution
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 |
---|---|
38 | |
25 | |
19 | |
15 | |
8 |
User | Count |
---|---|
69 | |
48 | |
45 | |
18 | |
16 |