Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
7 |