Daily average of previous 5 Years / Problem with leap year

[glasi]

Hello everybody!

As a newbie, I'm approaching Power Query and Power Pivot. After the initial successes, I am now stuck with a problem and hope for expert advice.

I've already added my data to the Power Pivot data model.

Date	StorageLevel
01/01/2015	165.31
01/02/2015	164.98
01/03/2015	163.43
...
11/26/2020	221.98
11/27/2020	220.69

I know want to calculate a power pivot measure which returns the daily average of the previous 5 years, i. e. the average for each individual day in the period 2015 - 2019.

The following approach mathematically gives the correct results...

=AVERAGEX (
    SUMMARIZE (
        FILTER (
            Table1; (
                Table1[Date] >= DATE(2015;1;1)
                && Table1[Date] <= DATE(2019;12;31)
            )
        );
        Table1[Date]    
    );
    CALCULATE ( SUM ( Table1[StorageLevel] ) )
)

... however, the leap day 29th February is distorting my results. While 5 values are used to calculate the average for each day, there is only 1 value for February 29 in the period 2015-2019.

Does anyone of you know a code snippet how I can replace the average value for the leap day 29th February with the value of the previous day?

Many thanks in advance.

 

[Matt Allington]

the results are not distorted, they are correct. The Feb 29th average is correct. What do you want to happen? You have choices
you could do the calculation based on day of year, so in a leap year you will have an extra day at the end (day 366), so that will just move the problem
you could ignore this date
you could combine it with 1 March.
all of the above are less correct, but would remove the outlier. It depends what you want to do. Frankly I would just filter it out. It is easiest and no more right or wrong than the other options. Just wrap everything inside another calculate where Date <> 29 Feb

 

[glasi]

Ok, then I'll filter out all leap days in my Power Query code.

Final result will look like this then...