cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Show data as of last Tuesday

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)

2 ACCEPTED SOLUTIONS
Helper II

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".

Super User

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" )
14 REPLIES 14
Helper II

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".

Super User

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

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

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.

Super User

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

Helper II

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.

Super User

Helper II

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

Super User

Super User

hi @Maha1

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

Helper II

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

Super User

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:

Super User

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:

Helper II

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.