Show data as of last Tuesday

[Maha1]

Hi,

I have a dataset that includes future dates. I want to filter to get only the data from last Tuesday and before. For example, if today is Thursday I need to show only data as of last Tuesday (excluding yesterday and today)

[Maha1 · Accepted solution]

Thank you both @tamerj1 and @FreemanZ for the help. I was able to figure out a solution that worked with me. Let me know your feedback.
First I created a measure that give the Date of Last Tuesday :

Last Tue Date = IF(WEEKDAY(TODAY(),1) = 3 , today(),
                IF(WEEKDAY(TODAY(),1) = 4 , today() -1 ,
                IF(WEEKDAY(TODAY(),1) = 5 , today() -2 ,
                IF(WEEKDAY(TODAY(),1) = 6 , today() -3 ,
                IF(WEEKDAY(TODAY(),1) = 7 , today() -4 ,
                IF(WEEKDAY(TODAY(),1) = 1 , today() -5 ,
                IF(WEEKDAY(TODAY(),1) = 2 , today() -6 
                )))))))

 Then I created a column to filter only the dates on last tuesday and before:

Date Filter = IF( 'Table'[Date] <= [Last Tue Date], 
                  "Keep", "Remove")

Finally, I used the column in the page filter to filter "Remove".

[Maha1 · Accepted solution]

Thank you both @tamerj1 and @FreemanZ for the help. I was able to figure out a solution that worked with me. Let me know your feedback.
First I created a measure that give the Date of Last Tuesday :

Last Tue Date = IF(WEEKDAY(TODAY(),1) = 3 , today(),
                IF(WEEKDAY(TODAY(),1) = 4 , today() -1 ,
                IF(WEEKDAY(TODAY(),1) = 5 , today() -2 ,
                IF(WEEKDAY(TODAY(),1) = 6 , today() -3 ,
                IF(WEEKDAY(TODAY(),1) = 7 , today() -4 ,
                IF(WEEKDAY(TODAY(),1) = 1 , today() -5 ,
                IF(WEEKDAY(TODAY(),1) = 2 , today() -6 
                )))))))

 Then I created a column to filter only the dates on last tuesday and before:

Date Filter = IF( 'Table'[Date] <= [Last Tue Date], 
                  "Keep", "Remove")

Finally, I used the column in the page filter to filter "Remove".

[tamerj1 · Accepted solution]

@Maha1 

You don't need to create a measure and a column. If you wish to have it as a column then use

Date Filter Column =
VAR LastTuesday =
    FILTER ( CALENDAR ( TODAY () - 6, TODAY () ), WEEKDAY ( [Date], 2 ) = 2 )
RETURN
    IF ( 'Table'[Date] <= LastTuesday, "Keep", "Remove" )

[tamerj1]

Hi @Maha1 
You can place the following measure in the filter pane of the visual or the page and select "is not blank" then apply the filter.

Date Filter Measure =
VAR LastTuesday =
    FILTER ( CALENDAR ( TODAY () - 6, TODAY () ), WEEKDAY ( [Date], 2 ) = 2 )
RETURN
    IF ( NOT ISEMPTY ( FILTER ( 'Table', 'Table'[Date] <= LastTuesday ) ), 1 )

[Maha1]

Thank you for the respond, however, it didn't work with me. I wasn't able to put the measure in the page filter. Also, I didn't get an option for "Basic Selection" in the visual filter. Below is a screenshot of the results.

 

[tamerj1]

@Maha1 

Yer perhaps can't be used in a page filter but you still can use it to filter your visuals. Please read my first reply again. You need to select "is not blank". If you wish to have the freedom to change for example the day from Tuesday to any other day, then can be easily done using a parameter slicer. Please confirm exactly what are you trying to achieve in order to guide you through 

[Maha1]

As I have a dataset for the whole year, I want to show only the data as of last Tuesday. In today's case, all visuals shall provide totals as of Jan 3. I used the measure to filter "Line Graph" and it worked. However, for other visuals such as "KPI Cards" I wasn't able to filter.

[tamerj1]

@Maha1 

Would you please provide more details about these visuals?

[Maha1]

How can I share with you the sample of my Power BI file?

[tamerj1]

@Maha1 

You can upload the onedrive or dropbox and share the link

[FreemanZ]

hi @Maha1 

this is not difficult if you can provide some sample data and expected result.

[Maha1]

Hi @FreemanZ 

I have a dataset for the whole year. I want the table to show only data as of last Tuesday which is (1/3/2023). Therefore, if today was (1/11/2023) I want to show the data automatically as of last Tuesday (1/10/2023). Below is an example of dataset.

Day	Date	Result
Sunday	1/1/2023	100
Monday	1/2/2023	200
Tuesday	1/3/2023	150
Wed	1/4/2023	300
Thursday	1/5/2023	200
Friday	1/6/2023	200
Saturday	1/7/2023	120
Sunday	1/8/2023	350
Monday	1/9/2023	250

[FreemanZ]

hi @Maha1 

Depending on how are you going to present the result, you create a calculated table with this:

Table = 
VAR _max = 
MAXX(
    FILTER(
        TableName,
        WEEKDAY(TableName[Date], 2) = 2
            &&TableName[Date]<=TODAY()
    ),
    TableName[Date]
)
RETURN
FILTER(TableName, TableName[Date]<=_max )

it worked like this:

[FreemanZ]

@Maha1 

if you expect a measure, try this:

ResultMeasure = 
VAR _max = 
MAXX(
    FILTER(
        ALL(TableName),
        WEEKDAY(TableName[Date], 2) = 2
            &&TableName[Date]<=TODAY()
    ),
    TableName[Date]
)
VAR _date = MAX(TableName[Date])
VAR _result = MAX(TableName[Result])
RETURN
IF(_date<=_max, _result)

it worked like this:

[Maha1]

I tried creating a measure using your Calculated Table dax code and got this error message :"the expression refers to multiple columns multiple columns cannot be converted to a scalar value". 

Note that I have multiple tables and they have relationships and measures. "Result" is actually a measure not a column by dividing one column in a table by a column in another table. I also have many measures used in the visuals. 

Therefore, I want to filter everything in the dashboard as of last tuesday.