cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BugmanJ
Helper I
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.

 

DateTimeCustomerID
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
SpartaBI
Community Champion
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



2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

6 REPLIES 6
SpartaBI
Community Champion
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



2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

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

SpartaBI
Community Champion
Community Champion

@BugmanJ my pleasure 🙂

amitchandak
Super User
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 !!
tamerj1
Community Champion
Community Champion

@BugmanJ 

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors