Table Date Filter

[shippen70]

I am working on creating a filtered table based on date ranges defined by a slicer value.  From the Slicer I determine the [FilterStartDate] and [FilterEndDate] in the measures at the bottom.  When I create the new table I'm just trying to take the values [Est. Ship Date] from the table in the defined date range.  I have tried to use DatesBetween but end up with similar results.  If I replace [FilterStartDate] and [FilterEndDate] with Date(yyyy, mm, dd) the filter works fine but when I put the measure in it does not work.  It appears I'm having some kind of a format issue but have not been able to find a resolution.  Any thoughts are appriciated.  

  
--------------------------------------------------------------------------------------------------------------------------------------

MC_OrdersDateFilter =

FILTER(

    _MC_Orders,

    (

    DATEVALUE(_MC_Orders[Est. Ship Date]) >= DATEVALUE([FilterStartDate]) &&

    Datevalue( _MC_Orders[Est. Ship Date]) <= DATEVALUE([FilterEndDate])  

     )  

)

-------------------------------------------------------------------------------------------------------------------

FilterStartDate =

var TodayDate = Today()

var Wk12Date = CALCULATE(FIRSTDATE(DateRange[12Week]), DateRange[IsToday] = TRUE)

var Wk12MonthEnd = Calculate(ENDOFMONTH(DateRange[CalendarDate]), DateRange[Year] = Year(Wk12Date), DateRange[MonthNumber] = Month(Wk12Date))

var YearStart = Calculate(STARTOFYEAR(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate) )

var YearEnd = Calculate(ENDOFYEAR(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate) )

var QtrStart = Calculate(STARTOFQUARTER(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate), DateRange[Quarter] = QUARTER(TodayDate))

var QtrEnd = Calculate(ENDOFQUARTER(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate), DateRange[Quarter] = QUARTER(TodayDate))

var MonthStart = Calculate(STARTOFMONTH(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate))

var MonthEnd = Calculate(ENDOFMONTH(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate))

var  NextMonthStart =  Calculate(STARTOFMONTH(NEXTMONTH(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate))

var  NextMonthEnd =  Calculate(ENDOFMONTH(NEXTMONTH(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate))

var  PrevMonthStart =  Calculate(STARTOFMONTH(PREVIOUSMONTH(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate))

var  PrevMonthEnd =  Calculate(ENDOFMONTH(PREVIOUSMONTH(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate))

var  NextYearStart =  Calculate(STARTOFMONTH(NEXTYEAR(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate))

var  NextYearEnd =  Calculate(ENDOFMONTH(NEXTYEAR(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate))

var PrevYearStart =  Calculate(STARTOFMONTH(PREVIOUSYEAR(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate))

var  PrevYearEnd =  Calculate(ENDOFMONTH(PREVIOUSYEAR(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate))

var Result = Switch(

    True(),

    SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "This Yr",  YearStart,

    SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "This Mnth", MonthStart,

    SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "Prev Yr", PrevYearStart,

    SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "Next Yr", NextYearStart,

    SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "Next Mnth", NextMonthStart,

    SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "Prev Mnth", PrevMonthStart,

    SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "12 Wk", MonthStart,

    SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "YTD", YearStart

    )

Return

DATEVALUE(Result)

//Date(2023, 01,01)

[johnt75]

Calculated tables are only calculated during data refresh, they don't pay any attention to slicers or filters. If you need something to react dynamically to silcers and filters you must do it in a measure

[shippen70]

I tried building a calculated column inside the table provided a true/false if it met.  This will only return "true" When I replace [FilterStartDate] with Date(2023,01,01) the filter works.  Am I not able to put a measure into the column formula to make it dynamic.  I'm pretty sure I have done this in the past.

FilterStart =

    If(

       Datevalue( [EstShipDate]) > Datevalue([FilterStartDate]) , true, false)

[johnt75]

No, that won't work. Calculated columns are the same as calculated tables, they are only calculated during data refresh and so they cannot take account of filters or slicers.