Counting numbers per hour between a start and end time

[Anonymous]

Hello,

I am trying to find the count of people at any give hour. For example, lets say we have a column withh people's names called People, a column with start time called StartTime and a column with the end time called EndTime. What I want to do is see the count of people in any given hour range. 

An example I found online that exactly matches my problems in this: https://stackoverflow.com/questions/33435898/counting-numbers-per-hour-between-a-start-and-end-time 

This example has a answer in SQL but I was not sure how to translate this to DAX. 

Please let me know if you need any other information.

Thanks

[sps-reporter]

Thanks for this thread. Exactly what I was looking for and need to go the next step and do the counts with filters on attributes from the original table like date and colors for example. If someone got to the last part of this and figure out how to do this would enjoy any tips.

Thanks

[Anonymous]

Hello,

I am learning powerBI. I followed the above solution but somehow it's not working for my data. I wanted to show employee count by the hour and date. Here is what the sample data look like:

Any help on this is appreciated 😊

Thanks
Richa

[v-yuta-msft]

@Anonymous,

To be general, you can create a measure using DAX formula like pattern below:

Number =
COUNTROWS (
    FILTER ( table, table[date] <= EndTime && table[date] >= StartTime )
)

Regards,

Jimmy Tao

[Anonymous]

Lets just say we only have a table with columns Person, StartTime, and EndTime. Both StartTime and EndTime are in a time format like 9:00:00 AM. I want to get a count of the number of people that are present at each hour period.

I have an example of this in the link I gave but I can do another example. Lets say Adam has a StartTime of 9 AM and EndTime of 11 AM. He would then have a count of 9AM-10 AM: 1 and 10AM-11AM: 1. This is because he was present for those two hours and these counts would continue for all of the people in the table. 

Please look at the link and look at the SQL code answer because that may lead to some ideas on how to tackle this in Power BI.

[v-yuta-msft]

@Anonymous,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[v-yuta-msft · Accepted solution]

@Anonymous,

Please follow steps below:

Click Modeling-> New Table, input DAX formula below:

Table_temp = GENERATESERIES(HOUR(MIN('Table'[Start_time])), HOUR(MAX('Table'[End_time])))

This will general a table with only one column [Value] to achieve all the hour included in the table, then right click on the new table to create a calculate column using DAX formula below:

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

 

You can also refer to the attached sample pbix file if you want.

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft Thank you this works but I want to pose one last question. How could we possibly connect this temp table to the original table so that different filter could happen? If there was a unique identifier in the original table would it be possible to get this in the temp table and then make a relationship between the two tables?

[v-yuta-msft]

@Anonymous,

Yes, you use DAX function ADDCOLUMNS, SUMMARIZE or SELECTCOLUMNS based on your specific requirement, although you can't view the temp_table in query editor, you can create relationship between original table and temp table.

 

Regards,

Jimmy Tao

[kt1622]

Hi, I have a similar case, but I have one more date column aside from People, Start time, End time.

I can create temp time table with above method.

But i am not able to apply fiter to date. Can you explain more how to use DAX function ADDCOLUMNS, SUMMARIZE or SELECTCOLUMNS to create relationship such that filter works?