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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kenj
Frequent Visitor

dax

I'm Unable to calculate moving/running average using

Average Cost moving = AVERAGEX (

                                  WINDOW( 1,ABS, 0,REL,

                                  SUMMARIZE(ALLSELECTED('Custom Calendar Table'), 'Custom Calendar Table'[FY], 'Custom                                                  Calendar Table'[Month Name]),    

                                  ORDERBY('Custom Calendar Table'[FY], ASC, 'Custom Calendar Table'[Month Name], ASC)), [Actual                                           Cost] )

Results is

Average Cost moving     Actual Cost     Month Name   FY

220599.2747                   220599.2747   Dec                   FY 22-23

219440.6214                   219440.6214    Jan                    FY 22-23

216470.4219                   216470.4219    Feb                    FY 22-23

I am not getting Running Average of Actual cost, plz suggest the correct way of DAX code

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

I think the problem is the sort order, you're sorting by month name but I think you want to sort by month number. try

Average Cost moving =
AVERAGEX (
    WINDOW (
        1,
        ABS,
        0,
        REL,
        SUMMARIZE (
            ALLSELECTED ( 'Custom Calendar Table' ),
            'Custom Calendar Table'[FY],
            'Custom Calendar Table'[Month Name],
            'Custom Calendar Table'[Month number]
        ),
        ORDERBY ( 'Custom Calendar Table'[FY], ASC,
        'Custom Calendar Table'[Month Number], ASC )
    ),
    [Actual Cost]
)

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

I think the problem is the sort order, you're sorting by month name but I think you want to sort by month number. try

Average Cost moving =
AVERAGEX (
    WINDOW (
        1,
        ABS,
        0,
        REL,
        SUMMARIZE (
            ALLSELECTED ( 'Custom Calendar Table' ),
            'Custom Calendar Table'[FY],
            'Custom Calendar Table'[Month Name],
            'Custom Calendar Table'[Month number]
        ),
        ORDERBY ( 'Custom Calendar Table'[FY], ASC,
        'Custom Calendar Table'[Month Number], ASC )
    ),
    [Actual Cost]
)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.