Filter between hours in the day

[android1]

Hi,

This calc column 8AM-10PM = IF(HOUR(vw_PivotChargesFrontPage[Visits_StartTime]) >= 8  && HOUR(vw_PivotChargesFrontPage[Visits_EndTime]) <= 22 ,TRUE(),FALSE())

returns all calls with a start time on or after 8am & an end time on or before 10pm. Problem is it also returns calls with an end time between 10pm & 10:59pm. In pic below I do not want 22:55, 22: 50 etc

If I add && Minute(vw_PivotChargesFrontPage[Visits_EndTime]) <= 0 then it will exclude calls that end at 8:30am, 19:55 etc 

which I don't want.

[Greg_Deckler · Accepted solution]

Would need sample data posted that I can copy and paste, just been hacking up to this point.

[Greg_Deckler]

8AM-10PM = IF(HOUR(vw_PivotChargesFrontPage[Visits_StartTime]) >= 8  && HOUR(vw_PivotChargesFrontPage[Visits_EndTime]) <= 21 ,TRUE(),FALSE())

[android1]

Hi Greg,

Thanks for the feedback.

What about calls with an end time of 22:00. They won't be included if I use <=21.

I'd like all calls returned with a start time from 8am onwards & with an end time of 22:00 at the latest.

Expected results ->

[Greg_Deckler]

8AM-10PM = 
IF(
     HOUR(vw_PivotChargesFrontPage[Visits_StartTime]) >= 8  
     && 
     (
       HOUR(vw_PivotChargesFrontPage[Visits_EndTime]) <= 21 
       || 
       (
         HOUR(vw_PivotChargesFrontPage[Visits_EndTime]) = 22 
         && 
         MINUTE(vw_PivotChargesFrontPage[Visits_EndTime]) = 0)
       )
      ),
      TRUE(),
      FALSE()
)

Maybe.

[android1]

Oops, inadvertantly accepted as solution.

Not quite returning what I need.

[v-chuncz-msft · Accepted solution]

@android1,

Add the following Boolean expression as well.

    vw_PivotChargesFrontPage[Visits_StartTime]
        <= vw_PivotChargesFrontPage[Visits_EndTime]

[android1]

Yes, thank you. @Greg_Deckler Your code works perfectly with the added expression from @v-chuncz-msft

[Greg_Deckler]

Cool! The power of teamwork!

[Greg_Deckler · Accepted solution]

Would need sample data posted that I can copy and paste, just been hacking up to this point.

[android1]

Hi @Greg_Deckler,

Sample data -> https://www.dropbox.com/s/z821ia0x86qjmk2/WIP.pbix?dl=0