Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Time Comparison in Filter expression not working

I have a simple table that looks like this...

TimeID NumberBaseline
12:00 AM001123
1:00 AM001256
11:00 PM001269
12:00 AM002796
1:00 AM002456

 

The time is for 24 hours. I need to find the sum of Baseline for each ID number only between the 3 hr time window of 1:00PM to 3:00 PM.

 

I used this measure: 

CALCULATE(SUM(Table[Baseline]),Table[Time]>=TIMEVALUE("1:00 PM")&&Table[Time]<=TIMEVALUE("3:00 PM"))
 
However, the filter is not working. It shows the sum of baseline for each ID for all 24 hours, not for the 3 hour window. Not sure what is wrong here. 
 
 
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    3 years ago

    I meant there are no rows between 1:00 PM and 3:00 PM in the sample data you provided in your original post.
    Is your time column set to time data type in your model?  The measure works as expected on the sample you provided.

     

7 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    Anonymous 

    Try it like this.

    Time Amount =
CALCULATE (
    SUM ( 'Table'[Baseline] ),
    'Table'[Time] >= TIME ( 13, 0, 0 ) && 
    'Table'[Time] <= TIME ( 15, 0, 0 )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      jdbuchanan71 

      Thank you for the answer. However, it comes out empty.

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    That is because, in your dataset, there are no rows with a time between 1:00 PM and 3:00 PM

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      There is data between that time frame. Just to check, I calculated the sum without the time filter expression. And then put time as a filter. Chose between 1 to 3 pm. It shows the right result.

      But whenever I put the time filter expression back into the measure, doesn't matter whatever time duration it is. It either shows empty or shows the total sum of 24 hours, not according to the time stated in the filter expression.

       

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    I meant there are no rows between 1:00 PM and 3:00 PM in the sample data you provided in your original post.
    Is your time column set to time data type in your model?  The measure works as expected on the sample you provided.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you so much for the suggestion. That's where I went wrong. I built this table by summarizing another table. I changed the time data type for this one but forgot to change the type in the original table. Now that I fixed it, it's working great. Thank you so much for all your help jdbuchanan71