Calculate Moving Average based on Date and Time Dimension

Anonymous — Tue, 16 Mar 2021 10:14:15 GMT

Hi,

I would describe myself as a beginner with PowerBI. I have a question about the handling of time dimensions.

My objective is to see which post leads to an increase regarding the order amount wihtin the next 48 hours. Therefore I would calculate a moving average based on the date and time.
For each day and each hour of the day there should be the average number of orders calculated for the next 48 hours. For example: If there are 300 Orders between 1st January 2021 8 am and 3rd January 2021 8 am the calculated value for 1st January 2021 8 am should be number_of_orders_in_intervall/number of days=300/2=150. For 9 am the same calculation will be made with the upper border of 3rd January 2021 9 am ...

In my data model I distinguish between a time and a date dimension. Both are linked to both of my fact table via a foreign key.

Unfortunately I could not make it until yet. This is my DAX-Measure for the moving Average for Days only which seems to work well.

Moving Average Order (N Days Interval) = VAR C = CALCULATE(AVERAGEX(DIM_DATE,[Total Orders]+0),ALL(Dim_Date),FILTER(ALL(Dim_Date[Date]),Dim_Date[Date]>=MAX(Dim_Date[Date]) &&Dim_Date[Date]<MAX(Dim_Date[Date])+'Days for Moving Average'[Days for Moving Average-Wert])) VAR B = ADDCOLUMNS(Dim_Date,"Moving Average",C) RETURN CALCULATE(AVERAGEX(B,[Moving Average]),ALL(Dim_Date[Date]))

Now I want the calculate the moving average based on dates and hours.

// Since there is no datetime Table in my Model I try to use crossjoin to get a table // with one row for each hour of each day (For two days there should be 48 rows) var _table = CROSSJOIN(FILTER(Dim_Date,Dim_Date[Date]>=DATE(2021,01,01)),Dim_Time) var _start = MAX(Dim_Date[Date])+TIME(MAX(Dim_Time[Hour of Day]),0,0)) // 'Days for Moving Average'[Days for Moving Average-Wert] is a // parameter to change the intervall for the average calculation e.g. from 01-01-2021 3 days in the future instead of 2 days var _end = _start + 'Days for Moving Average'[Days for Moving Average-Wert] var _average = CALCULATE( AVERAGEX(_table,[Total Orders]), FILTER( _table, (Dim_Date[Date] + TIME(Dim_Time[Hour of Day],0,0) >= _start && Dim_Date[Date] + TIME(Dim_Time[Hour of Day],0,0) <= _end ) )) RETURN _average

Unfortunately the code above does not work. If I use Date and TIME(Hour of Day,0,0) for the x-axis and the calculated moving average is zero.

I would greatly appreciate any advice. Many thanks in advance.

topic Calculate Moving Average based on Date and Time Dimension in DAX Commands and Tips

Calculate Moving Average based on Date and Time Dimension