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.

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