cancel
Showing results for
Did you mean:
Helper I

## Calculate Hourly Average for an Average Day From current Month

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

1 ACCEPTED SOLUTION
Community Champion

@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

6 REPLIES 6
Community Champion

@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

Helper I

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

Community Champion

@BugmanJ my pleasure 🙂

Super User

@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])))) )

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Community Champion

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

Helper I

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

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!