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 ...
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.

     

     

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 🙂