Forum Discussion
Filter using custom date options
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
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
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
- 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.