Forum Discussion

ctashwin's avatar
ctashwin
Frequent Visitor
6 years ago
Solved

Customer Count - Start Time and end time

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

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    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.

3 Replies