cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Time Intelligence Table - Minutes Over 60

I created a Time Intelligence Table (just like a Calendar Table but for time). The goal is to create a reference calculated column which will be broken into 15 min increments ( <=15mins / 16-30 / 31-45 / 46-60 / Over 1hr ). Current problem is that Time Intelligence Table currently has minutes that stop at 59. My data table has minutes that have 60+mins. The result is that any results Over 1hr will be bucketed into categories that is not correct (see screenshot below).

15minbandNOTCORRECT.pngNOT CORRECT 

I found a work-around solution by creating the calculated 15 min increment column in the data table. My hope was to have it in the Time Intelligence Table and have it work across multiple different data tables. 

15minbandCORRECT.pngCORRECT - TARGET OUTCOME

 

Any ideas on how I can solve this? 

 

Below is the syntax for the 15min increments:

15min Time Band =
SWITCH(
TRUE(),
'Data Table'[TIMEDIFF.MINUTE] >= 0 && 'Data Table'[TIMEDIFF.MINUTE] <= 15, "15 mins or Less",
'Data Table'[TIMEDIFF.MINUTE] >= 16 && 'Data Table'[TIMEDIFF.MINUTE] <= 30, "16 - 30 mins",
'Data Table'[TIMEDIFF.MINUTE] >= 31 && 'Data Table'[TIMEDIFF.MINUTE] <= 45, "31 - 45 mins",
'Data Table'[TIMEDIFF.MINUTE] >= 46 && 'Data Table'[TIMEDIFF.MINUTE] <= 60, "46 - 60 mins",
'Data Table'[TIMEDIFF.MINUTE] >= 61, "Over 1hr"
)
 
Below is the syntax for the Time Intelligence Table: 
 
Time =
VAR _series =
GENERATESERIES( 1, 1440, 1 )
VAR _time =
ADDCOLUMNS( _series, "TimeandDay", TIME( 0, [Value], 0 ) )
RETURN
ADDCOLUMNS(
_time,
"TimeKey", FORMAT( [TimeandDay], "hhmm" ),
"Actual Time", FORMAT( [TimeandDay], "HH:MM AM/PM" ),
"HourNum", HOUR( [TimeandDay] ),
"Hour", FORMAT( [TimeandDay], "HH:00" ),
"Hour Extended", FORMAT( [TimeandDay], "HH:00 AM/PM" ),
"Hour Short AMPM", FORMAT( [TimeandDay], "H AM/PM" ),
"Minute", MINUTE( [TimeandDay] ),
"AMPM", FORMAT( [TimeandDay], "AMPM" ),
"1HR TimeBand", FORMAT( [TimeandDay], "HAM/PM" ) & "-" & FORMAT( [TimeandDay] + TIME( 1, 0, 0 ), "HAM/PM" )
)
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Time Intelligence Table - Minutes Over 60

So I'm curious.  Your 'workaround' would be the way I would accomplish this task.

 

If you've got a time dimension table, that's just like listing out all the possibilities of a clock in various formats.  It seems like you're trying to assign each minute of the day a category like "Over 1hr".  How can 1:15PM be "31-45 mins"?

 

It makes much more sense to assign that category to your data table.   Your table seems to have a Start Time and End Time, and a TIMEDIFF.MINUTE that counts the minutes.  You compare the TIMEDIFF.MINUTE against your buckets for each row, and assign it a category like "15 mins or less" becasue a specific entry can have that property.

 

Why not just apply the same DAX to each field on each table to give it a category?  It's just copy/pasting and changing the field that's being tested against.

View solution in original post

4 REPLIES 4
otravers Member
Member

Re: Time Intelligence Table - Minutes Over 60

@Anonymous I'm sure there's a DAX solution, but I tend to solve this type of issue either at the source (e.g. SQL view) or in Power Query. For one, it's likely to perform better. Have you considered creating a column for your minute banding in PQ?

Super User
Super User

Re: Time Intelligence Table - Minutes Over 60

So I'm curious.  Your 'workaround' would be the way I would accomplish this task.

 

If you've got a time dimension table, that's just like listing out all the possibilities of a clock in various formats.  It seems like you're trying to assign each minute of the day a category like "Over 1hr".  How can 1:15PM be "31-45 mins"?

 

It makes much more sense to assign that category to your data table.   Your table seems to have a Start Time and End Time, and a TIMEDIFF.MINUTE that counts the minutes.  You compare the TIMEDIFF.MINUTE against your buckets for each row, and assign it a category like "15 mins or less" becasue a specific entry can have that property.

 

Why not just apply the same DAX to each field on each table to give it a category?  It's just copy/pasting and changing the field that's being tested against.

View solution in original post

Anonymous
Not applicable

Re: Time Intelligence Table - Minutes Over 60

I guess I was over thinking it. My goal was to have it housed in the Time table so I can apply it across different tables. It is still possible to accomplish what I am hoping to do but I am pretty sure I would have to create a separate minute key or something in that nature. 

 

Thanks!

Super User
Super User

Re: Time Intelligence Table - Minutes Over 60

I guess it just doesn't make sense to me how adding these values to your time dimension is helpful. If I wrote out all the possible times in a day, how would it make sense to say that 4:35p has the attribute "15 mins or less" and 4:36 is "16-30 mins" for however you would categorize each minute? 

 

What's happening when you try to add the categories to your time table but then don't have any counted in the 1 hour+ bucket is that you've checked the minute of each timestamp and given it a corresponding category.  Then you take a duration (let's say 69 minutes) and returning the bucket for minute 69 of the day, which would be 1:09a, which has a category of "15 mins or less". 

 

If this was working like you thought, how would "applying this to multiple tables" work?  Would you want something like "Return all rows where the Time is 4:35p AND are in the "Less than 15min" bucket? Or some sort of filter that can be easily applied to different durations by using USERELATIONSHIP in queries? 

 

I'm more than happy to help you work out a proper data structure for this, but treating timestamps and durations as the same type of data just isn't useful.

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,420)