Filter using custom date options

[MarkSL]

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 possible.  I am sure this has been done before but I seem to be going around in circles looking for a solution.

The approach I have started is to create my own DateControl table, with one row per date and columns for each custom date option:

And I have created a relationship from this to my main table, joining on date.

I have then created a third standalone table, simply listing the different categories, which my slicer points to:

I was trying to following this guide, which create a cusom measure, but I can't get it to work.  From what I can tell, it will select the records with the maximum value from the column appropiate to what has been selected in the filter.  So in my case, if I select 'This Month', it will select rows from column DateControl[This Week] where the value is 'Y.  

I have also tried updating my measure to use the following code, but still the report does not change when I select from the filter:

IF (ISFILTERED ( DateOptions[Category]) && HASONEVALUE (DateOptions[Category] ),
 SWITCH(LASTNONBLANK (DateOptions[Category], 0),
 "This Year", MAX(DateControl[This Year]),
 "This Month", MAX(DateControl[This Month]),
 "This Week", MAX(DateControl[This Week]),
 "Last Week", MAX(DateControl[Last Week]),
 "Yesterday", MAX(DateControl[Yesterday]),
 "Today", MAX(DateControl[Today]))
 BLANK()
)

Any help or suggestions greatly appreciated!

Mark

[MarkSL]

Hi,

Apologies, please bare with me, quite new to all of this!

Ok, very usefully, I've now been able to see my measure, DateCategory, in action by adding it to a simple report.  When I change my Date Category slicer, I can see that the measure dynamically updates, putting a 'Y' in all cells that match the selection.  So if I select "This Week", I get two Y's, mimicking the two Y's in my This Week column:

All I need to do now is to be able to filter on this measure, so only those two records are used - but it seems you can't.  Am I close or going about this all wrong?!

Thanks

Mark

[v-jiascu-msft]

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]

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]

Hi Mark,

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

Best Regards,

Dale

[MarkSL]

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]

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

Best Regards,

Dale

[MarkSL]

Hi Dale,

Thanks again for taking the time to look at this for me again.  Whilst your solution works great in terms of a new measure, it is not quite what I require.  I want the choices taken in my custom date slicer to apply to all records in my entire dashboard, not just a summing up values in new measure.  So if I select 'Yesterday', all charts, tables etc will filter accordingly, if I select This Month, again all charts, tables change again.  I realise I can use a Relative Date Slicer on my Date field, but I simply want something less cluttered.  

I don't know if this can be achieved by writing some custom code that simply relates to the my Date field, eg If DateOptions'[Category]= "Today", then filter to all records to where SalesDate = TODAY())

or

if I need to create dedicated columns in my date control table, such as Today, This Week, This Month etc and then write custom code which says If DateOptions'[Category] = "Today" then filter on column 'Today' where value = Y or If Category = "This Week" then filter on column 'This Week' where value = Y.

Thanks again

Mark

[MarkSL]

If anyone could help me further with this query it would be most appreciated. I am sure what I want to do has been done before by others. I just want a nice simple date control filter which I can control the options of.

[MarkSL · Accepted solution]

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.