Date Filtering in Excel from your Analysis Services Tabular Model
Following on from how to format the date in Analysis Services UDM Model, today I found myself trying to do the same thing for the tabular model. Where Excel was viewing the dates as a label and not as an actual Date. And after some looking around it is fairly simple to implement so that you can then use the Date Functions within Excel.
- Go into your Tabular Project
- Go into your Date Sheet that you have imported.
- Then click on any Column within your Date Sheet, so that it is selected
i. NOTE: I had already set the properties in my Date Column to the following:
- Now at the topclick on Table, then click on Date, then you will see Mark As Date Table.
- This will then open the Mark as Date Table Window
- Click on the drop down and select your correctly formatted Date Column.
i. In my example below it was called Date
- Then click Ok.
- Now deploy and then process your Tabular Model.
- Now when you go into Excel you will see the following:
can you fix those images? that would be so helpful!
Hi there, I will see if I can get it resolved.
It’s really a great and helpful piece of information. I’m glad that you shared this useful info with us. Please keep us informed like this. Thanks for sharing.
Pleasure, I am glad it helped you out
Date filters works in this case but sorting still doesn’t. It sorts alphabetically.
Also, if you just have a date column and not a date dimension (I have several dates in the model and don’t necessary need them all tied to the Date dimension) then is there any solution to getting the dates to work as dates with proper filtering and sorting?
Hi there
As far as I know the dates should always sort in date order, they have in my models. I would ensure that the data type is set to date.
With regards to the date column and using the proper sorting and filtering I would suggest linking them to the date table in order for them to work.
Data type is set to date. By default it sorts in date order but then when you use either ascending or descending sort in the pivot it ends up sorting alphabetically. Also, with a normal date column, one doesn’t get the date filtering option like next week, last month etc. That only seems to work with a date column in a date table.
Having a date table for every single date filed just seems like an overkill unless I’m missing some trick. Some dates are just information and at most a user may want to do like a between filter.
Thanks for the further details, I now have a better understanding of what you are trying to achieve.
As far as I know it appears to only work on a Date Table in SSAS Multi-dimensional. I know with SSAS Tabular there are ways to create multiple relationships. As well as possibly use DAX measures to create it on different date columns.
I am not sure how to achieve what you are looking for due to the limitation from SSAS Multi-Dimensional.