Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Thank you for your time. I'm trying to update a formula to use the new DAX WINDOW function on a rolling average count.
I feel the problem is I'm not sure how to capture/collect the count value, so then the average doesn't work. Thank you for assistance!
3-month window average new tix =
AVERAGEX(
WINDOW (
-2,REL,0,REL,
SUMMARIZE(ALLSELECTED('Ticket Entries'),'Ticket Entries'[creationDate],'Calendar Table'[YYYY-MM],"count",COUNT('Ticket Entries'[ticket#])),
ORDERBY('Ticket Entries'[creationDate]),
KEEP,
PARTITIONBY('Calendar Table'[YYYY-MM])
),
AVERAGE((COUNT('Ticket Entries'[ticket#]))) // doesn't work. I don't know how to grab count from above to use here
)
Example data looks like:
Ticket Entries Table | Calendar Table | |||
entry | ticket# | creationDate | YYYY-MM | |
1 | m1 | 5-Nov-22 | 2022-11 | |
2 | m2 | 28-Nov-22 | 2022-11 | |
3 | m3 | 2-Dec-22 | 2022-12 | |
4 | m4 | 5-Dec-22 | 2022-12 | |
5 | m5 | 20-Dec-22 | 2022-12 | |
6 | m6 | 25-Dec-22 | 2022-12 | |
7 | m7 | 11-Jan-23 | 2023-01 | |
8 | m8 | 14-Jan-23 | 2023-01 | |
9 | m9 | 18-Jan-23 | 2023-01 | |
10 | m10 | 20-Jan-23 | 2023-01 |
and I'm trying to achieve:
YYYY-MM | Tickets Created | 3-month rolling average |
2022-11 | 2 | 2 |
2022-12 | 4 | 3 |
2023-01 | 4 | 3.3 |
Solved! Go to Solution.
Interestingly to me (so I clearly needed to learn more about the Window function) I ended up solving this by stripping down the Window function for my needs. This was the formula that solved my "how do I get the average of counts" question.
moving Avg YYYY-MM =
AVERAGEX(
WINDOW(-2,REL,0,REL //-2,REL,0,REL, 1,ABS,-1,ABS
,,
ORDERBY('Calendar Table'[YYYY-MM],ASC),KEEP,
),
CALCULATE(COUNT('Ticket Entries'[creationDate]))
)
Interestingly to me (so I clearly needed to learn more about the Window function) I ended up solving this by stripping down the Window function for my needs. This was the formula that solved my "how do I get the average of counts" question.
moving Avg YYYY-MM =
AVERAGEX(
WINDOW(-2,REL,0,REL //-2,REL,0,REL, 1,ABS,-1,ABS
,,
ORDERBY('Calendar Table'[YYYY-MM],ASC),KEEP,
),
CALCULATE(COUNT('Ticket Entries'[creationDate]))
)
You may have to tweak the formula shown here to suit your requirement
Thanks! This is summing vs counting (my problem), but you have some good information in here that I'll be able to learn from and which might get me over my hump!
Thank you! I hadn't come across this video in my searching and it was very well done! I learned some more infomation so I'm getting closer and closer to solving my average counting situation!
Thank you very much
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |