CODEWITHSUNDEEP
ssascubepowerpivottabular
cmn
cmn

Reputation: 93

SSAS Tabular / PowerPivot Date Filters

I am trying to design a SSAS 2014 Tabular Cube that has easy to use custom date filters. For example, I want users (in excel for example) to be able to select a custom date range such as "This Month", "Last Month", "This Year", "Last 12 Months", "Last 2 Years", "Last 5 Years", etc.

I was hoping to model it like this (I'm new to stack overflow, so I can't insert images):

Sales Table

Date Table

Date Filter Table

Sales Table is related to Date Table on Date column

Date Filter Table is related to Date Table on Date column

The Date Filter is set up as follows. Each custom filter type would contain a row for each date within the date range. E.g. "This Month" would contain a row for each date in the current month. Something like this:

--------------+------------+
|    Date     |   Filter   |
--------------+------------+
| 1/May/2015  | This Month |
| through to: | This Month |
| 31/May/2015 | This Month |
|             |            |
| 1/Apr/2015  | Last Month |
| through to: | Last Month |
| 20/Apr/2015 | Last Month |
|             |            |
| 1/Jan/2015  | This Year  |
| through to: | This Year  |
| 31/Dec/2015 | This Year  |
|             |            |
| 1/Jan/2014  | Last Year  |
| through to: | Last Year  |
| 31/Dec/2014 | Last Year  |
+-------------+------------+

When I use this model in Excel, it is not applying the filter:

+-----------+------------+-----------+------------+--------------+
|   Date    | Last Month | Last Year | This Month | Year To Date |
+-----------+------------+-----------+------------+--------------+
| 1/01/2010 |          1 |         1 |          1 |            1 |
| 2/01/2010 |          1 |         1 |          1 |            1 |
| 3/01/2010 |          1 |         1 |          1 |            1 |
| 4/01/2010 |          1 |         1 |          1 |            1 |
| 5/01/2010 |          1 |         1 |          1 |            1 |
| 6/01/2010 |          1 |         1 |          1 |            1 |
| 7/01/2010 |          1 |         1 |          1 |            1 |
+-----------+------------+-----------+------------+--------------+

Any suggestions or help would be appreciated.

Upvotes: 4

Views: 359

Answers (1)

Abhijeet Nagre
Abhijeet Nagre

Reputation: 926

Check direction of relation between Date table and Date Filter table i.e. Date table should be lookup table (This is because one date will occur only once in Date table but occur multiple times in Date Filter table). Arrow should be pointing from Date Filter table to Date table.

Upvotes: 0

Related Questions