running totals between hourly ranges

[bartus1]

I have a report showing sales that refresh every 15 minutes.

for current runtime, need to display total sales as follows.

#1 - For 0700 to 1900 hours -> count sales in that time range only

#2 - For 1900 to 0700 hours -> count sales in that time range only

Columns:

Sale#, saleDateTime, $

03, 2018-09-14 13:55:18, 45

07, 2018-09-14 17:58:01, 258

... and so forth.

my thoughts:

if hour(now)>=7 && <=19, rCount=calculate(cales#), datesbetween(saleDateTime), max( "need to pick up hour"), min ("hour"), and then FALSE part in similar fashion.

Can't seem to pick up current hour to compare and then hour from saleDateTime.

Anyone?

[Greg_Deckler · Accepted solution]

I think that you want a couple of table filters like:

Measure = 
VAR __7to19 = FILTER(ALL('Table'),HOUR([saleDateTime) >=7 && HOUR([saleDateTime)<19)
VAR __19to7 = FILTER(ALL('Table'),HOUR([saleDateTime) <7 || HOUR([saleDateTime) >=19)
VAR __hour = HOUR(NOW())
RETURN 
IF(__hour >=7 && __hour<19,COUNTX(__7to19,[Sale#]),COUNTX(__19to7,[Sale#]))

Something along those lines.

[bartus1]

Big thanks.

I'll check this tomorrow and reply back.  Much appreciated.