How To Create Customized "Time Frame" Radio-Button Filter

This post will show how to create an easy to use Radiobutton filter (single selection type) with a list of predefined custom time frames. The created time frames can also be used for breaking by charts or pivots, providing a visual comparison between custom time frames.  

Example

alt   

Steps

  1. Create a time frame flag custom field for each desired time frame, give it the name of the time frame. you can use the following syntax for the above time frames:

Today: CASE WHEN daydiff(now(),DATE) = 0 THEN 1 ELSE 0 END
Last 2 days:  CASE WHEN daydiff(now(),DATE)< = 1 THEN 1 ELSE 0 END
Last 7 days:  CASE WHEN daydiff(now(),DATE) <= 6 THEN 1 ELSE 0 END
This month:  CASE WHEN monthdiff(now(),DATE) = 0 THEN 1 ELSE 0 END
Past month:  CASE WHEN monthdiff(now(),DATE) = 1 THEN 1 ELSE 0 END
*When using a Dim Dates table, the CASE WHEN syntax should be merged in the SELECT section
(i.e SELECT date,
CASE WHEN daydiff(now(),DATE) = 0 THEN 1 ELSE 0 END AS ‘Today’,
CASE WHEN daydiff(now(),DATE) <= 1 THEN 1 ELSE 0 END AS ‘Last 2 Days’
FROM…
UNION ALL)
2.   create a “Time Frame” table containing all time frames and the list of contained dates for each frame by adding a custom SQL table with the following syntax (for the above example):
SELECT  d1.Date AS date,'Today' AS [Time Frame Filter]
FROM Dates d1
WHERE d1.Today = 1
UNION ALL
SELECT  d2.date AS date,'Last 2 days' AS [Time Frame Filter]
FROM Dates d2
WHERE d2.[Last 2 days] = 1
UNION ALL
SELECT  d3.date AS date, 'Last 7 days' AS [Time Frame Filter]
FROM Dates d3
WHERE d3.[Last 7 days] = 1
UNION ALL
SELECT  d4.date AS dae, 'This month' AS [Time Frame Filter]
FROM Dates d4
WHERE d4.[This Month] = 1
UNION ALL
SELECT  d5.date AS date,'Past month' AS [Time Frame Filter]
FROM Dates d5
WHERE d5.[Past Month] = 1


3. Connect the time frame table to to the relevant table on “Date” and set the Date field in “Time Frame” table to “invisible”. The result should be similar to:
alt
4. Build the cube, now time frames are available for Dashboards.
5. Create a widget, add the time frame field as filter on dashboard level and set it to “Radiobutton”
alt
The created time frames can be also used as Columns in pivots, as categories in Pie Charts or as “break by” in charts, providing comparison between the time frames:
alt
*Attached below are Ecdata and .Dash file of the above example

Important Note

In order to prevent data duplication by the "time frame" options, the created "time frame" filters should only be used as a visible “break by” or “Radiobutton” filter and not as a “Multi-selection” filter. 
Download: