Forum Discussion

Veblengood's avatar
Veblengood
Helper I
3 years ago

Using a filter table to filter dates

Say I have created dates columns with YTD and MTD periods returning TRUE if in period and FALSE else, and want to use a these columns to filter my data with a slicer. The slicer is based on another table called Dimperiods. I came across one solution here, but the problem with this approach is that the Dates table needs to be represented in the visual  for this to work, which is not ideal. Is there a way to work around this? Selecting YTD should filter all the data so inYTD = TRUE.

 

What to Filter? =
VAR __filter = SELECTEDVALUE( Dimperiods[PeriodID] )
VAR __Isfilter =  
    SWITCH(
        __filter,
        "MTD", SELECTEDVALUE( Dates[inMTD] ) = TRUE(),
        "YTD", SELECTEDVALUE( Dates[inYTD] ) = TRUE(),
    )
    RETURN IF( __Isfilter = TRUE(), 1, 0 )
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Veblengood ,

    I created some data:

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _select=SELECTEDVALUE('Dimperiods'[PeriodID])
return
SWITCH(
    TRUE(),
_select="MTD"&&MAX('Date'[Date]) >= DATE(YEAR(TODAY()),MONTH(TODAY()),1)&&MAX('Date'[Date])<=TODAY(),1,
_select="YTD"&&MAX('Date'[Date]) >= DATE(YEAR(TODAY()),1,1)&&MAX('Date'[Date])<=TODAY(),1,0)
Measure =
SUMX(ALLSELECTED('Date'),[Value])

    2. Place [Flag]in Filters, set is=1, apply filter.

    3. Result:

    Whether this result meets your expectations

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Veblengood's avatar
      Veblengood
      Helper I
      3 years ago

      Thanks, but this only works if Date is included in the table. It does not work if you wish to use the measure to filter other visual where Date is not contained.