Relative Date Filter: When the value 'is NOT in the last ...'

[Anonymous]

Hi all,

I'm looking for an elegant way of applying a relative date filter to my report that will filter out data from the last week (or so). The reason I want this, is because recent data is still vulnerable to changes and it's a requirement that my end-users will not see it, as it's not that reliable.

The relative date filter gives me the option to show items when the value 'is in the last ... days', but I'd rather need 'is NOT in the last ... days'. 

Is there a smart way to make that happen?

Thank you!

[amitchandak · Accepted solution]

@Anonymous , Create a flag(New column) like this  in date table

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,
"Other weeks"
)

And filter "Other Weeks" at the page level

Refer for week start and end date of your choice - https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

[Anonymous]

Ended up using the following calendar expression (it's about the last column 'Type'), to filter out the most recent 14 days of data:

Calendar = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2019, 1, 1 ), NOW() ),
        UNION (
            ROW ( "Time", TIME ( 1, 5, 0 ) ),
            ROW ( "Time", TIME ( 2, 5, 0 ) ),
            ROW ( "Time", TIME ( 3, 5, 0 ) ),
            ROW ( "Time", TIME ( 4, 5, 0 ) ),
            ROW ( "Time", TIME ( 5, 5, 0 ) ),
            ROW ( "Time", TIME ( 6, 5, 0 ) ),
            ROW ( "Time", TIME ( 7, 5, 0 ) ),
            ROW ( "Time", TIME ( 8, 5, 0 ) ),
            ROW ( "Time", TIME ( 9, 5, 0 ) ),
            ROW ( "Time", TIME ( 10, 5, 0 ) ),
            ROW ( "Time", TIME ( 11, 5, 0 ) ),
            ROW ( "Time", TIME ( 12, 5, 0 ) ),
            ROW ( "Time", TIME ( 13, 5, 0 ) ),
            ROW ( "Time", TIME ( 14, 5, 0 ) ),
            ROW ( "Time", TIME ( 15, 5, 0 ) ),
            ROW ( "Time", TIME ( 16, 5, 0 ) ),
            ROW ( "Time", TIME ( 17, 5, 0 ) ),
            ROW ( "Time", TIME ( 18, 5, 0 ) ),
            ROW ( "Time", TIME ( 19, 5, 0 ) ),
            ROW ( "Time", TIME ( 20, 5, 0 ) ),
            ROW ( "Time", TIME ( 21, 5, 0 ) ),
            ROW ( "Time", TIME ( 22, 5, 0 ) ),
            ROW ( "Time", TIME ( 23, 5, 0 ) ),
            ROW ( "Time", TIME ( 24, 5, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] ),
    "Weekday", WEEKDAY([Date]),
    "Type", IF(DATEDIFF([Date],TODAY(),DAY)<=14,"Cool-off","Relevant")
)

[Fowmy]

@Anonymous 

You need to modify your measures so that the selected date range will be excluded.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn