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
Maha1
Helper II
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
Maha1
Helper II
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".

View solution in original post

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

View solution in original post

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

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

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.

 

 

@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 

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.

@Maha1 

Would you please provide more details about these visuals?

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

@Maha1 

You can upload the onedrive or dropbox and share the link

FreemanZ
Super User
Super User

hi @Maha1 

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

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.

DayDateResult
Sunday1/1/2023    100
Monday1/2/2023    200
Tuesday1/3/2023    150
Wed1/4/2023    300
Thursday1/5/2023    200
Friday1/6/2023    200
Saturday1/7/2023    120
Sunday1/8/2023    350
Monday1/9/2023    250

 

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_0-1673097260998.png

 

@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:

FreemanZ_1-1673097656084.png

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.

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.

Top Solution Authors