Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

rolling average using new Window DAX and counting values (so not summing, but finding avg of a count

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
entryticket#creationDate YYYY-MM
1m15-Nov-22 2022-11
2m228-Nov-22 2022-11
3m32-Dec-22 2022-12
4m45-Dec-22 2022-12
5m520-Dec-22 2022-12
6m625-Dec-22 2022-12
7m711-Jan-23 2023-01
8m814-Jan-23 2023-01
9m918-Jan-23 2023-01
10m1020-Jan-23 2023-01

 

and I'm trying to achieve:

YYYY-MMTickets Created3-month rolling average
2022-1122
2022-1243
2023-0143.3
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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]))
)

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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]))
)
Padycosmos
Solution Sage
Solution Sage

You may have to tweak the formula shown here to suit  your requirement

Padycosmos_0-1673571775123.png

 

Anonymous
Not applicable

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!

Padycosmos
Solution Sage
Solution Sage

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.