Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Relative Date Filter: Older Than Dynamic

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

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Anonymous ,
    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

     

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Dan 

       

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

       

      Sample Data:

       

      TitleLogged On (DateTime)
      Password Reset01/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's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous ,
        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