Relative Date Filter: Older Than Dynamic

[AndyB]

Hi There

I'm trying to get a count of support tickets older than 5 weeks.

The (relevant) table data in question for the visual is; Title (Count), Logged On (Date) (I also have this field available in DateTime).

With the visual level filter I can set this to "Is Before X Date" or "In the last 5 weeks" - but what I need is "Logged On Older than 5 weeks" -  as "In the last 5 weeks will be"

I've tried creating a custom column with the below. which I obtained from here

Table.SelectRows(#"Logged On (Date)", each [Date] < Date.AddDays(DateTime.LocalNow(),-35))

However, this returns the new custom column as a series of errors; am I formatting the above incorrectly? Or would this work with the data I have? (Sample Cell: 01/08/2018) 

Any help would be greatly appreciated!

Thanks

Andy

[v-danhe-msft]

Hi @AndyB ,

Could you please post some simple sample data and your desired result to have a test if possible?

You could see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Daniel He

[AndyB]

Hi Dan 

Thanks for that link - I'll be sure to read through it and adhere to the guidelines in future!

Sample Data:

Title	Logged On (DateTime)
Password Reset	01/08/2018 11:20
RE: Procurement	01/08/2018 13:21

Essentiallly what I want to achieve is a visual with "Count of Title" filtered by "Before 5 Weeks Ago" - but dynamically - so similar to the below, but in the same format as the relative date filtering, where it would be referencing "Before 5 Weeks Ago" as opposed to Before 14/04/2019 for instance.

Is there a way for me to create a custom column somewhere to do this? Or something similar?

Thanks

Andy

[v-danhe-msft · Accepted solution]

Hi @AndyB ,
I could not understand what is your desired result. Which data you want to compare of "Before 5 Weeks Ago"? Today or select a day to be dynamic? If the later, you could refer to below example:

Sample(week is a calculated column):

Create a new table:

New Table = SELECTCOLUMNS('Table1',"Date",[Date],"Week",[Week])

Use the [Date] in new table as slicer and create below measure:

Measure = IF(CALCULATE(MAX('Table1'[Week]))+5<=SELECTEDVALUE('New Table'[Week]),1,0)

Set the measure filter as 1 and you could get below result(based on the slicer you have chosen):

You could also download the pbix file to have a view.

Regards,

Daniel He