Running total based on time in and time out

[kattlees]

I have a database with Guest, Time In, Time Out, Area, Date

I have the in and out time rouding up to the next half hour.

Example:
Kathy      5:00    7:15         PO   7-9-18

Kathy      7:20    9:00         OR   7-9-18

Mike       6:00    8:00         PO   7-9-18

Mike       8:05    9:00         OR   7-9-18

What I need is this:

          5:00       5:30      6:00    6:30     7:00    7:30    8:00    8:30     9:00

PO       1             1          2        2          2         1         1         0         0

OR       0             0          0        0           0        1         2         2         0   

[v-piga-msft]

Hi @kattlees,

Based on your information, I have a little confused about your expected output. Could you describe the logic of your output in more details?

Besides, is the time range from 5:00 to 9:00 a original column or the calculated column in your data model?

Best  Regards,

Cherry

[kattlees]

The fields are name, Time In, Time Out, Area, Date, Time In Rounded, Time Out Rounded

So if it says:
Kathy, 5:00    7:15    PO   7-9-18

Mike   6:10     8:20   PO   7-9-18

it means Kathy was in PO area from 5:00 to 7:15. Time In Rounded becomes 5:00 and Time Out Rounded becomes 7:30

and Mike was in PO area from 6:10 - 8:20. Time In Rounded becomes 6:00 and Time Out Rounded becomes 8:30 

I would need Kathy to be counted as 1 for the PO area at 5, 5:30, 6, 6:30, 7:00 

I would need Mike to be counted as 1 for the PO area at 6:00, 6:30, 7, 7:30, and 8

So Count for PO on 7-9-18 would be

5:00 - 1

5:30 - 1

6:00 - 2

6:30 - 2

7:00 - 2

7:30 - 1

8:00 - 1

[Greg_Deckler]

See my two Quick Measures here for the general technique for these kinds of things:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

If those don't help, I'll try to swing back and see if I can come up with a specific solution to this.