Forum Discussion

shippen70's avatar
shippen70
Helper I
3 years ago

Table Date Filter

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)
date filter

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      shippen70
      Helper I
      3 years ago

      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's avatar
        johnt75
        Super User
        3 years ago

        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.