15 Minute Increments between 2 Timestamps

[themoosegoose]

I have a table with start time and end time and I need to find all the 15 minute time increments between those two times. I have converted the start time and end time into a column that shows the closest time interval as shown below and the duration (how many 15 minute increments are in between).

TIME FLOOR TIME CEILING DURATION
7:30:00 AM 8:15:00 AM 3

I also have a table that has all the times in the day and each interval has it's own "sort" identifier.

time sort interval
7:00:00 AM 1 7:00:00 AM
7:01:00 AM 1 7:00:00 AM
7:02:00 AM 1 7:00:00 AM
7:03:00 AM 1 7:00:00 AM
7:04:00 AM 1 7:00:00 AM
7:05:00 AM 1 7:00:00 AM
7:06:00 AM 1 7:00:00 AM
7:07:00 AM 1 7:00:00 AM
7:08:00 AM 1 7:00:00 AM
7:09:00 AM 1 7:00:00 AM
7:10:00 AM 1 7:00:00 AM
7:11:00 AM 1 7:00:00 AM
7:12:00 AM 1 7:00:00 AM
7:13:00 AM 1 7:00:00 AM
7:14:00 AM 1 7:00:00 AM
7:15:00 AM 1 7:00:00 AM
7:16:00 AM 2 7:15:00 AM
7:17:00 AM 2 7:15:00 AM
7:18:00 AM 2 7:15:00 AM
7:19:00 AM 2 7:15:00 AM
7:20:00 AM 2 7:15:00 AM

I basically need something that looks like the below.

   0      1       1      1       1      0
7:15  7:30  7:45  8:00  8:15  8:30

[v-ljerr-msft]

Hi @themoosegoose,

Based on my test, you should be able to use the formula below to create a measure.

Measure = 
CALCULATE (
    DISTINCTCOUNT(Table2[interval] ),
    FILTER (
        Table2,
        Table2[time] >= MAX ( Table1[TIME FLOOR] )
            && Table2[time] <= MAX ( Table1[TIME CEILING] )
    )
)+0

Then show the measure as Values, the interval column as Columns, and the index column from your first table as Rows on the Matrix visual to get the expected result in your scenario.

Here is the sample pbix file for your reference. 

Regards

[themoosegoose]

This worked when I had one case as an example, but not when there's many because it's only using the max value. I need the index to be a part of the measure because I have the date as the row

[v-ljerr-msft · Accepted solution]

Hi @themoosegoose,

Based on my test, you should be able to use the formula below to create a new measure, then show the new created measure as Values to get the expected result in your scenario. 

Measure 2 = SUMX(DATA,[Measure])

Regards

[themoosegoose]

We are back in business, thanks!!

[v-ljerr-msft]

Hi @themoosegoose,

Could you post your real table structures with some sample/mock, and the expected result? So that I can further assist on this issue. It's better to just share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[themoosegoose]

Hi @v-ljerr-msft

You can get the sample file from here

https://www.dropbox.com/s/2y5jujrm05p3v7u/15_Minute_Increments.pbix?dl=0