The expression contains multiple columns, but only a single column can be used in a True/False exp..

[TD21]

I would like to create a measure that sums daily total up to the day prior, and the measure [Date interval] is defined as:

[Date interval] = datediff (today(), 'Dates'[Date], day)

Forecast1 = SUMX('Dates',

    IF('Dates'[Date]=TODAY(),

        SUM('Sales'[TOTAL]),

    IF('Dates'[Date]=TODAY() + [Date Interval],

        CALCULATE(SUM('Sales'[TOTAL]),'Dates'[Date]= TODAY() + [Date Interval] - 1),

    0

)))

I get an error: "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

How do I fix the formula to specify yesterday's date (the section highlighted in red)?

[XLBob · Accepted solution]

If I understand what you are trying to do correctly, you can try below

Forecast1 =CALCULATE ( SUM ( 'Sales'[TOTAL] ), 'Dates'[Date] <= MAX ( 'Dates'[Date] ) - 1 )
what this does is it sums up all sales[Total] value up to the day before.

Pasting some sample data will always help other people better understand what you are trying to do