Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
Problem
I am trying to create a graph which has each hour along the X axis and the count of customers up the side. The data only needs to be shown for the current month but i want to show the average values over an average day
The data I have is simple, the customer call time is logged as dd/mm/yy hh:mm:ss and a unique number given to it.
DateTime | CustomerID |
04/05/2022 12:13:52 | A1259 |
So far, I have been able to create a graph with hours along the bottom and count up the side, and i can limit to this to a month, but I seem unable to create average hourly values for an average day for that month.
Sorry I dont have data to share
Solved! Go to Solution.
@BugmanJ somthing like:
Hourly Daily Average =
VAR _min_limit = SELECTEDVALUE('Dim Times'[Time Bucket])
VAR _max_limit = _min_limit + TIME(1,0,0)
VAR _result =
AVERAGEX(
VALUES('Table'[Date]),
CALCULATE(
COUNTROWS('Table'),
VAR _current_time = TIME(HOUR('Table'[DateTime]), MINUTE('Table'[DateTime]), SECOND('Table'[DateTime]))
RETURN
_current_time >= _min_limit && _current_time < _max_limit
)
)
RETURN
_result
But you need to have a time bucket parameter. Please see the link to a sample file I created for this:
Calculate Hourly Average for an Average Day From current Month.pbix
@BugmanJ somthing like:
Hourly Daily Average =
VAR _min_limit = SELECTEDVALUE('Dim Times'[Time Bucket])
VAR _max_limit = _min_limit + TIME(1,0,0)
VAR _result =
AVERAGEX(
VALUES('Table'[Date]),
CALCULATE(
COUNTROWS('Table'),
VAR _current_time = TIME(HOUR('Table'[DateTime]), MINUTE('Table'[DateTime]), SECOND('Table'[DateTime]))
RETURN
_current_time >= _min_limit && _current_time < _max_limit
)
)
RETURN
_result
But you need to have a time bucket parameter. Please see the link to a sample file I created for this:
Calculate Hourly Average for an Average Day From current Month.pbix
Hi @SpartaBI
This worked (eventually). I could get it to work with dummy data but not real data. Then eventually I realised that your looking at text value (Date) but in the calculate area, looking at actual dates (Datetime).
Thank you
@BugmanJ , You can date and hour column
Date = datevalue([DateTime])
Hour = hour([DateTime])
You can measure like
AverageX(values(Table[Date]), Calculate(AverageX(values(Table[Hour]),Calculate(Sum(Table[Value])))) )
When you say "of an average day" how do you define an average day? The montly average may not be equal to any of its days. This is a little bet vague
Well, best way I can describe it is that say at 4pm the total number of calls made for that month would be 56 and the month is Feburary, the graph should show 2 calls at 4pm if the month was feburary and viewing it post feburary. If I was viewing it in Feburary, then number of calls for that hour in that month should be divided by the number of days so far.
Hope that makes sense
User | Count |
---|---|
57 | |
33 | |
18 | |
18 | |
15 |
User | Count |
---|---|
94 | |
85 | |
37 | |
22 | |
20 |