Counting numbers per hour between a start and end time between two dates

[Aizhanz]

Hello, 

I've tried to calculate the number of active people per 24-hour range. I used this formula, and it works within one date. 

I used dax: 

countrid = CALCULATE(COUNTROWS('Table'), FILTER(ALL('Table'), HOUR('Table'[Start_time]) <= Table_temp[Value] && HOUR('Table'[End_time]) >= Table_temp[Value]))

And it works for AtendeeID=4982679 only. 

The problem starts when I try to calculate people who entered on 2020-08-23 and left on 2020-08-24. So, I need to edit my DAX formula in order to calculate  AtendeeID=4982659. Can you help, me? 

Initial data:

AttendeeID	Start_time	End_time
4982659	2020-08-23 21:41:17	2020-08-24 1:00:12
4982679	2020-08-24 1:33:12	2020-08-24 4:15:13

Basically, I need my result in this form:

Thank you!

[mh2587]

Check the value column data type 

[Aizhanz]

It's whole number type, I used formula = 

Table_temp = GENERATESERIES(0,23) 

[Anonymous · Accepted solution]

Hi  @Aizhanz ,

What is the final result you want? See the number of employees per hour (0 to 23)?Will the start time and end time of attend span multiple days? Or is it like 4982659, where the start and end times are not on the same day, but the end time is only on the next day at some point? If the others also have start and end times either on the same day or like 4982659, you can change your calculated column countid as follows, please find the details in the attachment...

countrid = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        IF (
            DAY ( 'Table'[Start_time] ) = DAY ( 'Table'[End_time] ),
            HOUR ( 'Table'[Start_time] ) <= Table_temp[Value]
                && HOUR ( 'Table'[End_time] ) >= Table_temp[Value],
            (
                HOUR ( 'Table'[Start_time] ) <= 'Table_temp'[Value]
                    && 'Table_temp'[Value] <= 24
            )
                || (
                    HOUR ( 'Table'[End_time] ) >= 0
                        && 'Table_temp'[Value] <= HOUR ( 'Table'[End_time] )
                )
        )
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards