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
Anonymous
Not applicable

Logical AND OR not working as expected

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!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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
                )
            )
        )
    )

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous  ,

According to your description, I create some data:

v-yangliu-msft_0-1619507795076.png

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.

v-yangliu-msft_1-1619507795078.png

 

 

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
Super User
Super User

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
                )
            )
        )
    )

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.