Forum Discussion

ShaneHi's avatar
ShaneHi
Frequent Visitor
1 year ago

Date Filters within a Date Parameter

Hi,

 

My aim is to have a Slicer that will change a data table to show "Last 7 Days", "Last 10 Weeks", "Last 12 Months" or "Custom Date Range" (as an example).

 

I've built out a slicer using this guide, to build a Date Parameter table that pulls specific columns from my Calendar Table. I then dropped this said code into the column field of a Matrix table, and it works perfectly (e.g. When I select D, it shows my data in the matrix in Days, When I select W, it shows my data in the matrix as Weeks etc).

 

I'd just like to be able to have a bit of a filter on the date ranges, but I can't figure out how!

 

The code i'm using within my Parameter is:

 

 

 

Date Parameter (MoM Pages) = {
    ("D", NAMEOF('Calendar'[Date]), 0),
    ("W", NAMEOF('Calendar'[W/C Monday]), 1),
    ("M", NAMEOF('Calendar'[Month]), 2)
}

 

 

Is it even possible to filter the date range here?

 

Thanks

Need Help

5 Replies

    • SamWiseOwl's avatar
      SamWiseOwl
      Icon for Super User rankSuper User
      1 year ago

      ShaneHi 

      Assuming you don't want to filter the data but rather remove the rows you could create a new column.

      Create a new column in your calendar table something like this:
      FDate =
      If( [Date] >= date(year(today()), 01,01), [Date], blank())

       

      Then add a row to your slicer table

      Date Parameter (MoM Pages) = {
    ("D", NAMEOF('Calendar'[Date]), 0),
    ("FD", NAMEOF('Calendar'[FDate]), 1),
    ("W", NAMEOF('Calendar'[W/C Monday]), 2),
    ("M", NAMEOF('Calendar'[Month]), 3)
}
  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    You could create a table similar to the below

    Date Range Table =
SELECTCOLUMNS (
    UNION (
        GENERATE (
            { "Last 7 Days" },
            DATESBETWEEN ( 'Date'[Date], TODAY () - 7, TODAY () )
        ),
        GENERATE (
            { "Last 10 Weeks" },
            DATESBETWEEN ( 'Date'[Date], TODAY () - 70, TODAY () )
        ),
        GENERATE (
            { "Last 12 Months" },
            DATESBETWEEN ( 'Date'[Date], EOMONTH ( TODAY (), -13 ) + 1, TODAY () )
        )
    ),
    "Date Range", [Value],
    'Date',
    'Date'[Date]
)

    You may want to tweak the start and end dates if you want full weeks, or if you want the latest date to be yesterday.

    Create a single direction many-to-many relationship with the Date table so that the 'Date Range Table' filters the date table and not the other way around.

    You can now use the date range column in visuals and it will filter the date table accordingly.

    This wouldn't work for a custom date range, but you could probably add an entry of { "Custom", BLANK() } into the union and then set up a normal slicer on the date table to achieve that.