Help with error when creating a measure for a date filter

[Belle2015]

Hi, 

I have had some help in creating a measure to create a filter to show the previous day's data or the weekend's data on a Monday but I am getting the below error 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value' and im not sure what I need to do to resolve this, sorry I am not so good with the more complex ones!

This is the table I want to filter - 

Count Date	Product	Counted by	More/Less	Difference Recorded	TotalStockPlusDiff	% of Total Stock
29/01/2025	623932	Clerk 1	More	-154307	-151558	101.81%
29/01/2025	623932	Clerk 1	Less	154409	151558	101.88%
28/01/2025	392-1269	Clerk 4	Less	3154	3119	101.12%
26/01/2025	392-2036	Clerk 3	Less	1508	7492	20.13%
26/01/2025	392-2069	Clerk 3	More	-9000	18000	-50.00%
22/01/2025	206360	Clerk 2	Less	34339	43181	79.52%
21/01/2025	392-1269	Clerk 4	More	-77	-13	592.31%
20/01/2025	203553	Clerk 3	More	-30441	13394	-227.27%
19/01/2025	1213-2369	Clerk 2	More	-1485	-788	188.45%
19/01/2025	2273069	Clerk 2	More	-1	200	-0.50%
19/01/2025	392-1036	Clerk 4	More	-1114	3307	-33.69%
16/01/2025	1213-5536	Clerk 4	Less	555	101	549.50%
16/01/2025	629610	Clerk 4	Less	1835	13735	13.36%
14/01/2025	1213-2369	Clerk 4	More	-1935	-1338	144.62%
10/01/2025	626129	Clerk 1	Less	11418	394	2897.97%
08/01/2025	1213-3636	Clerk 5	Less	400	1041	38.42%
08/01/2025	1213-5536	Clerk 4	More	-3000	-1949	153.93%
08/01/2025	1213-5536	Clerk 4	More	-385	-334	115.27%
07/01/2025	392-1269	Clerk 4	More	-53	-38	139.47%
04/01/2025	629069	Clerk 2	Less	343	399	85.96%

Thanks for any help 

[Anonymous · Accepted solution]

Hi, @Belle2015 

Based on your information, I create you table. At the same time, I added a piece of data from January 6th to test it.

Then create a new measure:

PreviousDayOrWeekendData = 
VAR TodayIsMonday = WEEKDAY(MAX('Table'[Count Date]), 2) = 1
VAR _PreviousDay = CALCULATE(SUM('Table'[TotalStockPlusDiff]), 'Table'[Count Date] = MAX('Table'[Count Date]) - 1)
VAR WeekendData = CALCULATE(SUM('Table'[TotalStockPlusDiff]), WEEKDAY('Table'[Count Date], 2) IN {6, 7} && 'Table'[Count Date] <= MAX('Table'[Count Date]))
RETURN
IF(TodayIsMonday, WeekendData, _PreviousDay)

Put this measure in table visual and create a slicer. Here is my preview:

When you select a slicer, the metrics also change. At the same time, it calculates the sum of the weekends up to the date selected by the current slicer.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Belle2015]

Thanks for your help with this, I think m not really explaining what I wanted it to do very well, I was hoping that it would work a bit like the relative date filter but without having to go in and select the days I wanted

[Bibiano_Geraldo]

Hi @Belle2015 ,

I just restructured your DAX to return the the desired scalar value, please try the bellow code and let me know if its all ok:

FilteredByDate = 
VAR TodayDate = TODAY()
VAR DayBefore = TodayDate - 1
VAR IsMonday = WEEKDAY(TodayDate, 2) = 1
RETURN
    CALCULATE(
        SUM('Table 2'[Difference Recorded]),
        FILTER(
            'Table 2',
            IF(
                IsMonday,
                'Table 2'[Count Date] = TodayDate - 3 || 
                'Table 2'[Count Date] = TodayDate - 2 || 
                'Table 2'[Count Date] = TodayDate - 1,
                'Table 2'[Count Date] = DayBefore
            )
        )
    )

[Belle2015]

Thanks so much for your help @Bibiano_Geraldo

Maybe I am not using this correctly but I though that I would just apply this measure to the filters to work but it is not filtering the table! Sorry I am very basic with this and trying to learn!

Preview

[Bibiano_Geraldo]

Hi whats is your expected resultt based on data you provided? i'll make some logic here to filter your data, just give me more context.

Thank you

[Belle2015]

Thanks for helping 🙂

I am trying to look at the counts done the previous day so as we can see what % of stock was used or added in, on a Monday I want to be able to see what was counted for on the Friday, Sat and Sunday. 

The table may have multiple counts done on the same product and it can be counted more than once on the same day. 

Hope that explains it ok.

[danextian]

Hi @Belle2015 

You can't return different table expressions based on conditions. Place each table expression in its own variable and then create a condition in the RETURN clause instead:

IF(
IsMonday,
CALCULATE(SUM('Table 2'[Difference Recorded]), filter01),
CALCULATE(SUM('Table 2'[Difference Recorded]), filter02)
)