Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Sum between two TIMES (Not Dates)

Hi All,

I have an issue where I'm trying to find the SUM energy between two times, a departing time and an arrival time. However, when I try to sum using a filter I get nothing. I know the data is there because I can manually find it in the table.
For Context:
Table 1 has every minute marked, along with how much energy was used in that minute

Table 2 has a departing time and arrival time that I'd like to calculate between

The calculation I have tried:

EnergyUse =
CALCULATE(
SUM(Table1[Energy]),
FILTER(Table1, Table1[Time]>='Table2'[dep]),
FILTER( Table1, Table1[Time]<='Table2'[arr])
)
I have also tried this but with '&&' instead of two filters, but that didn't work either. 
Please Help!
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    Looks like you are creating a calculated column, try like this:

    EnergyUse =
CALCULATE (
    SUM ( 'Table1'[Energy] ),
    FILTER (
        ALL ( Table1 ),
        'Table1'[Time] >= MIN ( 'Table2'[dep] )
            && 'Table1'[Time] <= MIN ( 'Table2'[arr] )
    )
)

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Time greater than or equal to departure 

    and

    Time lesser than or equal to arrival

     

    This doesnt make sense , shouldnt the filter be

    FILTER(Table1, Table1[Time]<='Table2'[dep]),
    FILTER( Table1, Table1[Time]>='Table2'[arr])
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Greater than or equal to is >= no?
      So it's >= depart time and <= arrive time to get the times in between?

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi Anonymous ,

    Looks like you are creating a calculated column, try like this:

    EnergyUse =
CALCULATE (
    SUM ( 'Table1'[Energy] ),
    FILTER (
        ALL ( Table1 ),
        'Table1'[Time] >= MIN ( 'Table2'[dep] )
            && 'Table1'[Time] <= MIN ( 'Table2'[arr] )
    )
)

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.