Dyanmic Time Issue

[siddharth3101_]

Hi, 

I have a data table that gives us the ideal 'Start Time' and 'End Time' of a product's journey (common for all products) for a particular Mall (sample data below). Similarly, another data table has line wise data for each product, including product name, mall name & product collection time. 

What I need is, that if a product belonging to a particular mall was collected up untill 10mins before the 'Start Time' in Table 1, then that Start Time is recorded, otherwise we get the next Start Time. The End Time corrosponding to the given Start Time should appear next to it. Refer the tables below:

I have the following data tables imported in Power BI Desktop:

Table1

Table 2

What I want to construct is the grid in table 3

Eg. For Ball belonging to mall A, Product Collection Time was 6:00, hence Start Time 10:00:00 got recorded, to which corrosponding End Time was 11:00:00. For Glove, however, Product Collection Time was 9:51:00 (less than 10mins from the Start Time), hence we moved onto the next Time Range in Mall A (11:00 to 12:30). Similarly, I need for Mall B and so on (all in one grid).

Would be obliged if someone could help!

[v-frfei-msft · Accepted solution]

Hi @siddharth3101_ ,

We can create two calcualted columns in table 2 as below.

start time = var a = 'Table 2'[Product Movement Time] + TIME(0,10,0)
return
CALCULATE(MIN('Table 1'[Start Time]),FILTER('Table 1','Table 1'[Mall] = 'Table 2'[Mall] && 'Table 1'[Start Time]>=a))

endtime = LOOKUPVALUE('Table 1'[End Time],'Table 1'[Start Time],'Table 2'[start time],'Table 1'[Mall],'Table 2'[Mall])

Pbix as attached.

[siddharth3101_]

Thanks, @v-frfei-msft ! 

This worked for me, much appreciated.