topic Re: Show data as of last Tuesday in DAX Commands and Tips

Show data as of last Tuesday

Maha1 — Sat, 07 Jan 2023 01:21:04 GMT

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)

Re: Show data as of last Tuesday

FreemanZ — Sat, 07 Jan 2023 01:27:06 GMT

hi Maha1

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

Re: Show data as of last Tuesday

tamerj1 — Sat, 07 Jan 2023 09:32:13 GMT

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 )

Re: Show data as of last Tuesday

Maha1 — Sat, 07 Jan 2023 23:40:54 GMT

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.

Re: Show data as of last Tuesday

Maha1 — Sat, 07 Jan 2023 12:39:54 GMT

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

Re: Show data as of last Tuesday

tamerj1 — Sat, 07 Jan 2023 12:59:36 GMT

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

Re: Show data as of last Tuesday

Maha1 — Sat, 07 Jan 2023 13:13:26 GMT

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.

Re: Show data as of last Tuesday

FreemanZ — Sat, 07 Jan 2023 13:14:53 GMT

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:

Re: Show data as of last Tuesday

FreemanZ — Sat, 07 Jan 2023 13:21:41 GMT

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:

Re: Show data as of last Tuesday

Maha1 — Sat, 07 Jan 2023 13:35:57 GMT

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.

Re: Show data as of last Tuesday

tamerj1 — Sat, 07 Jan 2023 13:41:20 GMT

Maha1

Would you please provide more details about these visuals?

Re: Show data as of last Tuesday

Maha1 — Sat, 07 Jan 2023 13:42:57 GMT

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

Re: Show data as of last Tuesday

tamerj1 — Sat, 07 Jan 2023 14:01:59 GMT

Maha1

You can upload the onedrive or dropbox and share the link

Re: Show data as of last Tuesday

Maha1 — Sat, 07 Jan 2023 23:38:49 GMT

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

Re: Show data as of last Tuesday

tamerj1 — Sun, 08 Jan 2023 10:46:15 GMT

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