Can't figure out what is wrong with the filter

[gvg]

Hi,

I have this measure and can't really figure out, why is it not working. I get error "A table of multiple values was supplied were a single value was expected". 

Sum Sales  =
    CALCULATE (
        SUM ( Sales[Amount] ),
        FILTER (
            ALL ( 'Date' ),
            DATESBETWEEN ( 'Date'[Date], DATE (2017,1,1), DATE(2017,9,30) )
        )
    )

[MattAllington]

Try this

Sum Sales  =
    CALCULATE (
        SUM ( Sales[Amount] ),    
            DATESBETWEEN ( 'Date'[Date], DATE (2017,1,1), DATE(2017,9,30) 
    )

[gvg]

OK. But how come this works and FILTER doesn't? Doesn't your CALCULATE translates internally to my CALCULATE with FILTER ?

[MattAllington]

---

@gvg wrote:

OK. But how come this works and FILTER doesn't? Doesn't your CALCULATE translates internally to my CALCULATE with FILTER ?

---

CALCULATE has 2 syntaxes

Simple syntax - this is what you are describing where the simple syntax is converted to the full syntax you mentioned.

Advanced (or table) syntax - this is what my formula does - it takes a table as a filter to CALCULATE.  DATESBETWEEN returns a table of dates. Whenever you get a table, you must use it directly in CALCULATE (advanced syntax). 

[gvg]

Advanced (or table) syntax 

---

OK. But doesn't FILTER return a table as well?