Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate Moving Average based on Date and Time Dimension

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.

uieas_0-1615821340303.png

 

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.  
0 REPLIES 0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.