Issue with Dax for a date slicer

[alex20p]

I have a slicer that filters by yesterday, month to date, last month and 2 months ago. I noticed when i hit yesterday it will show 4/9/2025 which is correct but when i use the month to date it will do 4/1/2025 -4/10/2025 but skip over 4/9/2025. I am assuming the way my code is setup it is picking the first case the date falls into so i can't have 4/9/2025 be used in both filters. How would i adjust my code to be able to do have both filters work? I am very new to powerbi so any help or guidance is appreciated. 

Update

To add onto this. I  created a custom column in my calendar and then added it to a slicer. I now see in the table that 4/9/2025 is labeled as yesterday and 4/1-4/10 excluding 4/9 has month to date.

DateFilter = 
VAR TodayDate = TODAY()
VAR Yesterday = TodayDate - 1
VAR MonthStart = DATE(YEAR(TodayDate), MONTH(TodayDate), 1)
var StartOfLastMonth = DATE(YEAR(TodayDate), MONTH(TodayDate) - 1, 1)
var endOfLastMonth = EOMONTH ( TODAY(), -1 )
var StartOf2MonthsAgo = DATE(YEAR(TodayDate), MONTH(TodayDate) - 2, 1)
var endOf2MonthsAgo = EOMONTH ( TODAY(), -2 )

RETURN 
SWITCH(
    TRUE(),
    'Calendar'[Date] = Yesterday, "Yesterday",
    'Calendar'[Date] >= MonthStart && 'Calendar'[Date] <= TodayDate, "Month-to-Date",
    'Calendar'[Date] >= StartOfLastMonth && 'Calendar'[Date]  <=endOfLastMonth, "Last Month",
    'Calendar'[Date] >= StartOf2MonthsAgo && 'Calendar'[Date]  <=endOf2MonthsAgo, "2 Months Ago",
    BLANK()
)

[v-pagayam-msft]

Hi @alex20p ,
Thank you @johnt75 for the helpful response!

To fix this, we used a disconnected slicer table and a DAX measure to dynamically filter visuals. Here is how I tried with my sample data:

1.Create a Disconnected Slicer Table(go to modeling,select new table) using below:
   DateFilterTable =

   DateFilterTable =

   DATATABLE(

    "DateFilter", STRING,

    {

        {"Yesterday"},

        {"Month-to-Date"},

        {"Last Month"},

        {"2 Months Ago"}

    }

)

2.Then created a simple table using below:
  

SalesData =

DATATABLE(

    "Date", DATETIME,

    "Sales", INTEGER,

    {

        { "2025-04-08", 200 },

        { "2025-04-09", 300 },

        { "2025-04-10", 250 },

        { "2025-04-01", 150 },

        { "2025-03-15", 100 },

        { "2025-02-28", 50 }

    }

)

3.Then created a sample calender table using below:

Calendar =

ADDCOLUMNS (

    CALENDAR (DATE(2025, 3, 1), DATE(2025, 4, 15)),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "MMM"),

    "Day", DAY([Date])

)

4.Add this measure:
 

IsInDateFilter =

VAR SelectedFilter = SELECTEDVALUE('DateFilterTable'[DateFilter])

VAR TodayDate = DATE(2025, 4, 10)  -- Simulated "today"

VAR Yesterday = TodayDate - 1

VAR MonthStart = DATE(YEAR(TodayDate), MONTH(TodayDate), 1)

VAR StartOfLastMonth = DATE(YEAR(TodayDate), MONTH(TodayDate) - 1, 1)

VAR EndOfLastMonth = EOMONTH(TodayDate, -1)

VAR StartOf2MonthsAgo = DATE(YEAR(TodayDate), MONTH(TodayDate) - 2, 1)

VAR EndOf2MonthsAgo = EOMONTH(TodayDate, -2)

VAR CurrentDate = MAX('Calendar'[Date])

RETURN

SWITCH(

    SelectedFilter,

    "Yesterday", IF(CurrentDate = Yesterday, 1, 0),

    "Month-to-Date", IF(CurrentDate >= MonthStart && CurrentDate <= TodayDate, 1, 0),

    "Last Month", IF(CurrentDate >= StartOfLastMonth && CurrentDate <= EndOfLastMonth, 1, 0),

    "2 Months Ago", IF(CurrentDate >= StartOf2MonthsAgo && CurrentDate <= EndOf2MonthsAgo, 1, 0),

    1

)

Add a slicer to the report using DateFilterTable[DateFilter], then create a table visual displaying Calendar[Date] and SalesData[Sales]. Next, drag the IsInDateFilter measure into the “Filters on this visual” section and set the filter condition to show only records where IsInDateFilter equals 1. This ensures the table visual dynamically updates based on the selected date filter.

Please refer the attached file for detailed  understanding.

If this solution meets your requirement,consider accepting it as solution.

Thank you .

Regards,
Pallavi.

[alex20p · Accepted solution]

Thanks @v-pagayam-msft @johnt75 for your responses.

I ended up having to make a seperate calendar slicer table, write the code to make a mini table for yesterday dates, month to date dates, lastmonth and 2 months ago and then i unioned them all. So when you look at this mini table it will show yesterdays date twice but 1 has a yesterday label and 1 has the month to date label. Once, the table was created i linked it to my calender table and my calendar table is already linked to my fact table. I made a slicer from the calendar slicer table and then it worked for me. 

I did end up trying Pallavi way first but I was having performance issues. It was taking about 1-2seconds to load the data each time i hit the filter button even though i am only working with maybe 10-20k rows of data. With creating a seperate table it makes it run about 300ms which is great. I appreicate both your help! I'm sure i'll need more suggestions in the future.

CalendarSlicer = 
VAR TodayDate = TODAY()
VAR Yesterday = TodayDate - 1
VAR MonthStart = DATE(YEAR(TodayDate), MONTH(TodayDate), 1)
VAR StartOfLastMonth = DATE(YEAR(TodayDate), MONTH(TodayDate) - 1, 1)
VAR EndOfLastMonth = EOMONTH(TodayDate, -1)
VAR StartOf2MonthsAgo = DATE(YEAR(TodayDate), MONTH(TodayDate) - 2, 1)
VAR EndOf2MonthsAgo = EOMONTH(TodayDate, -2)

RETURN
UNION (
    SELECTCOLUMNS (
        FILTER (
            'Calendar',
            'Calendar'[Date] = Yesterday
        ),
        "Date", 'Calendar'[Date],
        "Period", "Yesterday"
    ),
    SELECTCOLUMNS (
        FILTER (
            'Calendar',
            'Calendar'[Date] >= MonthStart && 'Calendar'[Date] <= TodayDate
        ),
        "Date", 'Calendar'[Date],
        "Period", "Month-to-Date"
    ))

[johnt75]

You can't do it with a column in the date table, as a date can then only ever belong to one group.

You could create a new table for use in the slicer, and use that to filter the date table. e.g.

Date slicer =
SELECTCOLUMNS (
    UNION (
        { ( "Yesterday", TODAY () - 1 ) },
        GENERATE (
            { "MTD" },
            CALCULATETABLE (
                DATESMTD ( 'Date'[Date] ),
                TREATAS ( { TODAY () }, 'Date'[Date] )
            )
        )
    ),
    "Label", [Value1],
    "Date", [Value2]
)

You can then create a many-to-many single direction relationship so that 'Date slicer' filters 'Date'.