Latest Enterprise DNA Initiatives

Getting the average of a moving average

DAX Calculations
#1

HI,

I would like to know how i get the actual average value of a 1 month moving average measure i created.

I have the column of the 1 month moving average showing here using this code

Total Cost 1M MA =

IF([Total Cost (AED)] = BLANK(), BLANK(), AVERAGEX(
DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -1, MONTH ),
[Total Cost (AED)] ) )

and what i want now is to have another column showing the average value of the above moving average column in each row the same value in order to create a straight line on my chart.

Also from this i can create a card displaying the average value.

Capture
Capture1920×1009 155 KB

Unfortunately i cannot upload a PBIX as its too big

Regards

Dan

#2

@Krays23

You can try the following measure:

    Moving Average = 
    IF(
        Total Cost (AED)] = BLANK(),
        BLANK(),
        CALCULATE(
            AverageX(
                'Date',
                    Total Cost (AED)] ),
                    DATESINPERIOD('Date'[Date] , 
                            LASTDATE('Date'[Date]),
                            -1 , 
                            MONTH)))

However, make sure that your Date table is a continuous Data Table and also marked as Date Table.

Thanks.

#3

Hi i think my point wasnt clear yes i already have that moving average working what id like to know now is what is the average value of that column

Dan

#4

Hi @Krays23 - Please check if below expression is helpful. It will provide Average of the Moving Average measure. Let us know if requirement is different.

Avg of Moving Average =
IF (
    ISBLANK ( [Total Sales] ),
    BLANK (),
    AVERAGEX (
        SUMMARIZE ( ALLSELECTED ( dates ), Dates[Date], "Mov", [Moving Average] ),
        [Mov]
    )
)

Thanks
Ankit J

1 Like
#5

Hi @Krays23, did the response provided by @Ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!