Forum Discussion

TD21's avatar
TD21
Icon for Helper II rankHelper II
7 years ago
Solved

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

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)?
Need Help
  • XLBob's avatar
    XLBob
    7 years ago

    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

1 Reply

  • XLBob's avatar
    XLBob
    Icon for Resolver II rankResolver II
    7 years ago

    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