how to get an add count in all timeslots based on activity start time and end time

[Anonymous]

hi,

Need a help.

i have three columns belt ,start time,end time.

start time and end time are timestamp columns.

i want to create 15 minute time slots first for ex:00:00-00:15,00:15-00:30 ....23:30-23:45,23:45-00:00 for 24 hrs.

next I want is ,if belt 2 starts using for some activity at 10:00 and ends at 11:13 then i get count in another column based on timeslots which we created as 10:00-10:15,10:15-10:30,10:30-10:45,10:45-11:00,11:00-11:15 in all this time slots i got count 1 as belt (starts using from) starts at 10:00 and ends at 11:13.

same way if same belt 2 again starts for other activity at  10:17 and ends at 11:17 then it also gives count in timeslots (with adding)(if timeslots and belt no repeats then only add)10:15-10:30,10:30-10:45,10:45-11:00,11:00-11:15( in all this timeslots i got count 2 as the belt no and timeslots repeats and timeslot 11:15-11:30 gives count 1 as it is not repeating.

ultimately i need heatmap with the help of matrix visual .so basically i need two columns timeslots of 15 minutes for 24 hrs for each(different belt for ex 2,3,4,6,2a,...)and count based on how start and end time.

How can i do this in power Bi.

Any help would be appreciated.

Thanks in advance.

[amitchandak]

Create a series in terms of Minute first. Then generate time-based on that column

https://docs.microsoft.com/en-us/dax/generateseries-function

hh =QUOTIENT(SeriesMin, 60) 

Calculate MM also. Append and create time.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi,

Need help.

As I mentioned,i create 15 min time slots.I am sorry as I am not able to describe my problem properly.

First I wanted to show you data so you ll get some idea. Data Structurecalculate column as per time slotsfinal output

first image is data structure in which i have first bag,last bag and merged column =fist bag-last bag(joined column) which reflects total time of that activity work.,minutes column is shows how much time that activity takes to complete.

second image shows the calculation which i made depends on first bag (calculation wrong) and third image is output which i want.heatmap graph with the help of matrix visual.

Now what i want is,in 1st image if you check minute column and merged column ,some example i give you as for belt 10,activity starts at 1:12 Am-2:00 Am ,so total time is 47 min.but as per my calculation it just show timeslot 1:00-1:15 only ,

but actually it should reflect 1:00-1:15,1:15-1:30,1:30-1:45,1:45-2:00  as activity ends at 2:00.

How can I do that?

I am sorry if i am unable to describe it propely.

Thanks in advance.