Calculate Hourly Average for an Average Day From current Month

[BugmanJ]

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

[SpartaBI · Accepted 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

      

Showcase Report – Contoso By SpartaBI

[BugmanJ]

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]

@BugmanJ my pleasure 🙂

[amitchandak]

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

[tamerj1]

@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 

[BugmanJ]

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