Forum Discussion

User1's avatar
User1
New Member
6 years ago
Solved

SUMIFs with date range conditionals

Hi all,

I have a table, with columns showing the numbers I want to add up, and columns containing date between which I would not want to add them up.

For example:

Number      Date from       Date to

5                       13/4                   15/4

10                    14/4                    20/4

5                        22/4                    25/4

 

I want to create a second table with a daily tally of the total of the "Number" column, but onkly if the date in question is outside the "Date from - date to" range. Either as a conditional column, or as a measure. For example:

Day               TOTAL

11/4              20

12/4              20

13/4               15

14/4                5

etc.

 

I tried to use the CALCULATE  ( SUM(), FILTER()) functionality, but could not get it to work. Is that the right approach? Or is there a better way to do this?

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi User1 ,

     

    Please create a new date table firstly. And there is not a relationship between the tables.

    Example:

    Date = CALENDAR(DATE(2020,4,1),DATE(2020,5,1))

     

    Then create a measure like this:

    Measure =
CALCULATE (
    SUM ( 'Table'[Number] ),
    FILTER (
        'Table',
        MAX ( 'Date'[Date] ) < 'Table'[Date From]
            || MAX ( 'Date'[Date] ) > 'Table'[Date To]
    )
)

     

    For more details, please see the attachment.

     

     

3 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi User1 ,

     

    Please create a new date table firstly. And there is not a relationship between the tables.

    Example:

    Date = CALENDAR(DATE(2020,4,1),DATE(2020,5,1))

     

    Then create a measure like this:

    Measure =
CALCULATE (
    SUM ( 'Table'[Number] ),
    FILTER (
        'Table',
        MAX ( 'Date'[Date] ) < 'Table'[Date From]
            || MAX ( 'Date'[Date] ) > 'Table'[Date To]
    )
)

     

    For more details, please see the attachment.

     

     

    • User1's avatar
      User1
      New Member
      6 years ago

      Thanks, in the end I got it to work, but I had to use the OR() statement instead of the || (which I assume does the same)?
      Thanks anyways 🙂