Customer Count - Start Time and end time

[ctashwin]

Hi All.

I trying to count number of customers at each hour. The data i have is start time and end time.

I used the below solution from one of the discussions and it worked. 

The only issue i am facing is the Count of People at Midnight. The data around midnight is coming as 0.

Is there a way to fix this?

Also, the customer Start time and end time will cross midnight.

Number of clients =
VAR vMinVal =
    MIN ( TimeTable[Value] )
VAR vMaxVal =
    MAX ( TimeTable[Value] )
VAR vClientEntry =
    CALCULATE (
        COUNTROWS ( ClientsTbl ),
        TimeTable[Value] <= vMinVal,
        ALL ( TimeTable )
    )
VAR vClientExit =
    CALCULATE (
        COUNTROWS ( ClientsTbl ),
        TimeTable[Value] >= vMaxVal,
        ALL ( TimeTable ),
        USERELATIONSHIP ( ClientsTbl[Exit Time], TimeTable[Value] )
    )
RETURN
    MIN ( vClientExit, vClientEntry )

Thanks for the Help

[v-yingjl · Accepted solution]

Hi @ctashwin ,

If you need to count customers for each hour of each day, you need to create two calculated columns to format date and create the following measure:

Count = 
var _time = SELECTEDVALUE('Time'[Time])
var _count = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        _time >= 'Table'[Start] && 
        _time <= 'Table'[End] 
    )
)
return
IF(
    ISBLANK(_count),0,_count
)

Here is the sample that you can refer: sample file.pbix

If you just count customers for each hour instead of each hour of each day, you can refer this issue:  Counting numbers per hour between a start and end time  

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@ctashwin - This is a variation of Open Tickets: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[amitchandak]

@ctashwin ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if this solution can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

In place of the date table, you need a time table

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/