SUMIFs with date range conditionals

[User1]

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?

[v-xuding-msft · Accepted solution]

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]

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 🙂 

[amitchandak]

@User1 , there is a similar problem solved in the file attached after the signature. If that can help