undefined

Step 1: Create a Disconnected Table for Time Units

You’ll need a slicer like:

Create a table like this (either manually or via Power Query):TimeGranularity = DATATABLE(

    "Granularity", STRING,

    {

        {"Years"},

        {"Quarters"},

        {"Months"},

        {"Weeks"},

        {"Days"},

        {"Hours"},

        {"Minutes"}

    }

)

Step 2: Create Another Table for Relative Selection

RelativeSelection = DATATABLE(

    "Selection", STRING,

    {

        {"Previous"},

        {"This"},

        {"Next"},

        {"To Date"}

    }

)

Step 3: Create Numeric Input Table for “Last/Next N”

This table lets users choose N = 1 to 10 (or more):

NTable = GENERATESERIES(1, 10, 1)

Step 4: Use These as Disconnected Slicers

Put TimeGranularity, RelativeSelection, and NTable as slicers on your report. They will drive logic in your DAX, not directly filter your date table.

Step 5: Create a Measure or Calculated Table to Filter Dates Based on Selection

You need a DAX measure that uses selected values from these slicers to compute a dynamic date range:

SelectedGranularity = SELECTEDVALUE(TimeGranularity[Granularity])

SelectedOption = SELECTEDVALUE(RelativeSelection[Selection])

SelectedN = SELECTEDVALUE(NTable[Value])

TodayDate = TODAY()

Then, based on combinations like

SWITCH(TRUE(),

    SelectedGranularity = "Months" && SelectedOption = "Previous",

        TodayDate >= EOMONTH(TodayDate, -SelectedN) + 1

        && TodayDate <= EOMONTH(TodayDate, -1),

    SelectedGranularity = "Months" && SelectedOption = "This",

        TodayDate >= DATE(YEAR(TodayDate), MONTH(TodayDate), 1)

        && TodayDate <= EOMONTH(TodayDate, 0),

    -- Add similar logic for Quarters, Weeks, Years, etc.

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.