Assigning a Time to a Time Period

[Anonymous]

Hey,

As an example. If I have some data on when people have checked into work.

Employee Name	Check In Time	Time Period
Daffy Duck	06:22:17	?
Goofy	12:56:24	?
Wayne Rooney	21:22:39	?

I want to add a calculated column that works out the time period the check in falls into.

The Time Period Start and End boundaries will be based on a seperate table below.

Time Period	Time Start	Time End
1	06:00:00	10:00:00
2	10:00:00	16:00:00
3	16:00:00	19:00:00
4	19:00:00	00:00:00

I want it set up in a way that when I update the Time Start an Time End in this table my Time Period in the first table would update.

Many thanks,

@amitchandak @parry2k 

[amitchandak · Accepted solution]

@Anonymous ,

You can get a new column like this in table 2. And you can join both table on time id

Time id = maxx(filter(Table2, table1[Check In Time]>=table2[Time Start] && table1[Check In Time]<table2[Time End]),Table2[Time Period])

Also, you can get a column like , But better join two table

start time = maxx(filter(Table2, table1[Time id]=table2[Time Period]),Table2[Time Start])

Appreciate your Kudos.

[amitchandak]

I did not get it completely. Please explain with an example

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

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

Appreciate your Kudos.

[parry2k]

@Anonymous not sure if I understood your question. You have a data table and you want to bring these time start and end column in your data table, correct? if that is the case, can you share how your data table looks like?

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490