Logical AND OR not working as expected

[Anonymous]

Hi,

I am using the following query to create a sub table from calendar tabe to include only the last days of every week or today()-1 for the current week: SubTable calendar end of week = var _currentWeek = max('calendar'[Week of Year(Wed-Tue)]) return CALCULATETABLE('Calendar',FILTER('calendar', OR ([Weekday(Wed-Tue)] = 7,AND([Week of Year(Wed-Tue)] = _currentWeek, [calendar_date].[Date] = TODAY() - 1))))

The max('calendar'[Week of Year(Wed-Tue)]) is returning the week of yesterday. However, the query is only giving dates till last week and nothing for the current week.

Any clue what is wrong?

Thanks in advance!

[Anonymous]

Hi  @Anonymous  ,

According to your description, I create some data:

Here are the steps you can follow：

1. Create calculated column.

Week = WEEKDAY('Table'[date],2)

Flag =
IF(WEEKNUM([date],2)=WEEKNUM(TODAY(),2),IF([date]=TODAY()-1,1,0),IF([Week]=7,1,0))

 2. Create calculated table.

Table 2 =
SELECTCOLUMNS(FILTER('Table','Table'[Flag]=1),"Date",[date])

3. Result.

Best Regards,

Liu Yang

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

[MFelix · Accepted solution]

Hi @Anonymous ,

Are you using measures to create your table? asking this becasue the notation looks like the use of measures especially on the [Weekday(Wed-Tue)] .

Can you also refer what is the last date of your calendar.

Also I do not understand the logic of [Weekday(Wed-Tue)] = 7 or Current date and today -1.

What is the final result you want to achieve is the to what I can read you want the last days of every week meaning that you want to filter out your table with all the Sundays and from last sunday until yesterday?

One question that I saw is that you have Calendar[Date] = Today-1 since all the days from the calendar are lower than 7 for this week you will not get results since the first day of the week is 7 try the following:

SubTable calendar end of week =
VAR _currentWeek =
    MAX ( 'calendar'[Week of Year(Wed-Tue)] )
RETURN
    CALCULATETABLE (
        'Calendar',
        FILTER (
            'calendar',
            OR (
                [Weekday(Wed-Tue)] = 7,
                AND (
                    [Week of Year(Wed-Tue)] = _currentWeek,
                    [calendar_date].[Date]
                        <= TODAY () - 1
                )
            )
        )
    )