Sum values between Time of Day (Hours)

[cflynn_29]

Hi Everyone,

I have a Table called "Table" that has two other columns called "Time" & "Value". 

I want to sum the vaules in the "Values" table but only between the hours of 7:30:00 AM to 3:30:00PM.

Theres no time intellegence on becuase most of visuals just need to see a 24 hour period 0:00:00 AM - 11:45:00 PM.

I tried DATESBETWEEN but that didnt work out. Do i need to restore the time intellegence or make a start "date" & end "date" column even though the dates are the same?

Thank you,

[Greg_Deckler]

Well, you can use the HOUR function, etc. But is that Time column Text or what data type is it?

I have a whole chapter in my book dealing with Time and Duration. Anyway, use TIMEVALUE to convert your text to a Time datatype then you can use HOUR and MINUTE functions to get your filtering.

Some of these links might help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085#M306

https://community.powerbi.com/t5/Quick-Measures-Gallery/TIMEADD/m-p/585280#M278

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration-Working-Hours/m-p/481543#M...

[cflynn_29]

@Greg_Deckler  the Time Column is a TIME Data Type, but in all those examples it looks like i need a start date & end date column to filter and get the sum of values that i need right?

[Anonymous]

You should have 2 dimensions in your model: Date and Time. Separate and connected to your fact table with correct data types (not text). If you want to sum values between 7:30 and 15:30 then create a column in the Time dimension that will group times from 7:30 through to 15:30 and then just filter by this grouping to obtain the correct total. You can call the grouping member something like "Effective Hours" and the rest of the time... some other meaningful name. Also, once you have this dimension, you can create another measure (that will always sum only within the effective hours) by just filtering the total by one of the members of the Time dimension, something like

calculate( 
    [total], 
    keepfilters( Time[Grouping Column] = "Effective Hours" )
)

Best

D

[cflynn_29]

@Anonymous  what is the correct data type for time? All the dates are the same btw and they arent really relavent.

I dont know how to go about creating an additional column to group the time between 7:30:00 - 15:30:00. But if i can figure out those first two steps then the later part will certainlly work.

Thanks

[Anonymous · Accepted solution]

If you only have Time without Dates, then it's even easier. Time should have the type of time (not text); if you want to see time in a particular format, use the formatting options of PBI and do not store time as text. Once you have a time column of a suitable granularity (every 5, 10, 15 minutes, every second or some other) that covers all instants round the clock, then you create a column "Time Group":

Time Group = // calculated column
	if(
		and(
			time(7,30,0) <= Time[Time],
			Time[Time] <= time(15,30,0)
		),
		"Effective Hours",
		"Sleeping Hours"
	)

Best

D