Forum Discussion

MarkSL's avatar
MarkSL
Helper V
8 years ago
Solved

Filter using custom date options

Hi,   I want to create simple slicer which includes custom date options, such as:       I know I can use a Relative Date Slicer, but I would like to keep my filter really simple if possib...
Need Help
  • MarkSL's avatar
    MarkSL
    8 years ago

    If anyone is interested, I was referred to the following article on Chris Webb's BI Blog which provided me with the solution I was looking for.

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Mark,

 

It seems the measure worked. What's the issue? Do you mean the table visual should only show two rows and hide the other rows?

 

Best Regards,

Dale

MarkSL's avatar
MarkSL
Helper V
8 years ago

Hi Dale,

 

Thanks for taking a look and yes that is correct, I want the table to be filted to those two rows, thereby also filtering my main data table which is joined to the date table, to only return data for those two dates.

 

Perhaps using this method is not viable?

 

Thanks


Mark

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Mark,

     

    That's because there are many other columns. Maybe you can use the filters.

    Filter_using_custom_date_options

     

    Best Regards,

    Dale

     

    • MarkSL's avatar
      MarkSL
      Helper V
      8 years ago

      Hi Dale,

       

      No that doesn't change it and I think I may have confused things.  Let me try to simplify my query...

       

      I want to offer the users a filter on date. I don't want to use the Relative Date Slicer as it offers options which are not suitable for my need.  I therefore want to offer the user a specific list of date options which will be applied to the date column SalesDate in my main sales table:

       

       

      So I essentially need a method of saying..

       

      If Category = "Today", then filter SalesDate = TODAY()

      If Category = "Yesterday", then filter SalesDate = TODAY()-1

      etc.

       

      Thanks

       

      Mark

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi Mark,

         

        Maybe you need a measure like this.

        Measure =
IF (
    HASONEVALUE ( 'DateOptions'[Category] ),
    SWITCH (
        MIN ( 'DateOptions'[Category] ),
        "Today", CALCULATE ( SUM ( 'Table'[Value] ), 'DateControl'[Date] = TODAY () ),
        "Yesterday", CALCULATE ( SUM ( 'Table'[Value] ), 'DateControl'[Date] = TODAY () - 1 ),
        "This Year", CALCULATE (
            SUM ( 'Table'[Value] ),
            YEAR ( 'DateControl'[Date] ) = YEAR ( TODAY () )
        ),
        "This Week", CALCULATE (
            SUM ( 'Table'[Value] ),
            FORMAT ( 'DateControl'[Date], "YYYYww" ) = FORMAT ( TODAY (), "YYYYww" )
        ),
        "This Month", CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'DateControl'[Date], EOMONTH ( TODAY (), 0 ), 1, MONTH )
        ),
        "Last Week", CALCULATE (
            SUM ( 'Table'[Value] ),
            INT ( FORMAT ( 'DateControl'[Date], "YYYYww" ) )
                = FORMAT ( TODAY (), "YYYYww" ) - 1
        ),
        0
    )
)

        Filter_using_custom_date_options2

         

         

         

        Best Regards,

        Dale