Moving average doesnt working

[gustavo80]

Hi everybody! I have a problem calculating a moving average in power bi ( getting wrong results ) and I can't find my mistake.

My moving average for 3 days is calculated this way:

Moving_Average_3_Days =

CALCULATE (

AVERAGEX ('Dataset', 'Dataset'[Day1 %] ),

DATESINPERIOD (

'Dataset'[install_date],

LASTDATE ( 'Dataset'[install_date]),

-3,

DAY

)

)

So I expected that the moving average displayed in the line in each day should be the average of 3 previous days, but it is a value much bigger than that. Could someone help me to understand what am I doing wrong ?

The .pbix with the example:

https://wetransfer.com/downloads/775c02db4096cdc1ff3b1c75b2014f1420200711230447/1b1b26276f0f46ff5d06...

Thanks in advance,

Regards

[v-alq-msft · Accepted solution]

Hi, @gustavo80 

Thank you for providing sample data. You may create a measure as below. The pbix file is attached in the end.

Moving_Average_3_Days 2 = 
var tab =
SUMMARIZE(
    'Dataset',
    'Dataset'[install_date],
    "avg",
    var _installdate = [install_date]
    return
    AVERAGEX(
        FILTER(
            SUMMARIZE(
                ALL('Dataset'),
                'Dataset'[install_date],
                "Day1%",
                [Day1 %]
            ),
            [install_date]<=_installdate&&[install_date]>=_installdate-2
        ),
        [Day1%]
    )
)
return
SUMX(
    tab,
    [avg]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gustavo80]

Thanks! You understood exactly the problem, and your solution works perfect. 

I will analyse the formula to understand better what was the problem with mine,

Thanks again, 

Regards!

[Ashish_Mathur]

Hi,

Download my PBI file from here.

Hope this helps.

[gustavo80]

Hi Ashish

Thanks for your answer, but I apply exact the same solution propposed in your files, and the percentages are still bad calculated, I do not understand why 😞

The file:

https://wetransfer.com/downloads/0241aa0acf39b9b55bcd5e772f42306820200712171336/17335b72d985a05a0459...

[Ashish_Mathur]

Hi,

I have already shared my solution file and a screenshot with you.  You may not have applied my solution carefully.  Try agin.

[az38]

Hi @gustavo80 

Not sure I understand your desired result correct, but DATESINPERIOD is a very tricky function according https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax

The returned table can only contain dates stored in the dates column. So, for example, if the dates column starts from July 1, 2017, and the start_date value is July 1, 2016, the returned table will start from July 1, 2017.

try smth like

Moving_Average_3_Days = 
var _curDate = MAX('Dataset'[install_date])
RETURN
CALCULATE (
    AVERAGEX ('Dataset', 'Dataset'[Day1 %] ),
    FILTER('Dataset',  
        'Dataset'[install_date] >= _curDate - 3 && 'Dataset'[install_date] <= _curDate
    )
)

[gustavo80]

Thanks for your time, az38!

I tried your solution, and I get exact the same result that I got at first, and with the previous solution suggested. So my conclussion is that the problem is not related to calculate date period, probablly has more to do with the averagex formula.

My desired result is: for every day, I need to have the average Day1% of the 3 previous days.

For example:

For July 5, I want to calculate the average of July 5, July 4 and July 3 Day1%. So it should be the avg between 13.94%, 15.29% amd 14.64% = 14,62% . But instead of that, I'm getting 48.25% or higher values, it makes no sense.

If there is another way easier to calculate it, it will be welcome too 😉

Thanks in advance,

Regards