Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
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
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
We are back in business, thanks!!
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
You can get the sample file from here
https://www.dropbox.com/s/2y5jujrm05p3v7u/15_Minute_Increments.pbix?dl=0
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
64 | |
63 | |
52 | |
39 | |
24 |
User | Count |
---|---|
80 | |
57 | |
45 | |
44 | |
35 |