Forum Discussion

kenj's avatar
kenj
Frequent Visitor
3 years ago
Solved

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

  • johnt75's avatar
    johnt75
    3 years ago

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

1 Reply

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

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