Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TD21
Helper II
Helper II

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)?
1 ACCEPTED SOLUTION
XLBob
Resolver II
Resolver II

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

View solution in original post

1 REPLY 1
XLBob
Resolver II
Resolver II

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.