Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |