Calculating moving average of time series data over a specified window

[Kade98002]

Hello. I am new to Power BI desktop and trying to calculate a moving average of time series data but it is not correctly calculating using the sample code below.  The calculcated time series data is the same as the source data and it is not averaging the 3 values as expected.  The time series data is on a 5mins interval and would like to calculate the average over a 15mins period using 3 values from the source data. 

kWhIntervalsTable = GENERATESERIES(0,100,1)

 

KWhIntervalPeriod = SELECTEDVALUE(kWhIntervalsTable[Value],3)

kWhIntervalTotal = SUMX(DeliveredkWh, DeliveredkWh[KWH])

15MinskWDemand =

 VAR MAXDATETIME = MAX(DateTable[DateTimeStamp])

 VAR INTERVAL = [KWhIntervalPeriod]

 VAR INTFILTER =

    FILTER(

        ALL(DateTable[DateTimeStamp]),

        AND(

        DateTable[DateTimeStamp] <= MAXDATETIME,

        DateTable[DateTimeStamp] > MAXDATETIME - "00:15:00")

    )

VAR OUTPUT =

    CALCULATE(

        AVERAGEX(DateTable,[kWhIntervalTotal]),

        INTFILTER)

RETURN  OUTPUT

Here is the sample data from the [kWh] table:

DateTimeStamp | kWhIntervalTotal | 15MinskWDemand
6/1/2024 1:05:00 AM 193.78 193.78
6/1/2024 1:00:00 AM 190.98 190.98
6/1/2024 12:55:00 AM 192.61 192.61
6/1/2024 12:50:00 AM 196.69 196.69
6/1/2024 12:45:00 AM 193.06 193.06
6/1/2024 12:40:00 AM 188.99 188.99
6/1/2024 12:35:00 AM 189.11 189.11
6/1/2024 12:30:00 AM 194.02 194.02
6/1/2024 12:25:00 AM 198.29 198.29
6/1/2024 12:20:00 AM 193.85 193.85
6/1/2024 12:15:00 AM 190.58 190.58
6/1/2024 12:10:00 AM 192.26 192.26
6/1/2024 12:05:00 AM 199.36 199.36

[rajendraongole1 · Accepted solution]

Hi @Kade98002 - Filtering the DateTable to include only timestamps within the last 15 minutes. You can use DATEDIFF or simple time arithmetic with MAX(DateTable[DateTimeStamp]).

create a measure summing data directly, consider applying a row-level calculation instead

kWhIntervalTotal = SUMX(DeliveredkWh, DeliveredkWh[KWH])

main measure: updated

15MinskWDemand =
VAR MAXDATETIME = MAX(DateTable[DateTimeStamp])
VAR INTERVAL = [KWhIntervalPeriod]
VAR INTFILTER =
FILTER(
ALL(DateTable[DateTimeStamp]),
DateTable[DateTimeStamp] <= MAXDATETIME &&
DateTable[DateTimeStamp] > MAXDATETIME - TIME(0, 15, 0)
)
VAR OUTPUT =
CALCULATE(
AVERAGEX(DeliveredkWh, DeliveredkWh[KWH]),
INTFILTER
)
RETURN OUTPUT

Hope this works.