Do I really need a full year in my Date table?

Hi there,

I have read in a few blog posts that I should awalys have a full calendar year in my Date table. Meaning if I have data from only 30 December 2019 to 6th March 2020, my Date table should (apparently) cover 01 January 2019 to 31 December 2020.

Question: Is this really true, that is, is there a compelling reason for warrenting the above practice?

As always, thank you.
Michelle

Hi @michellepace,

In most cases when you have a date field in your model, you will want to do some time intelligence calculations and for that type of analysis this is indeed a requirement.

Below an excerpt of this Microsoft article

To work with Data Analysis Expressions (DAX) time intelligence functions, there’s a prerequisite model requirement: You must have at least one date table in your model. A date table is a table that meets the following requirements:

  • It must have a column of data type date (or date/time )—known as the date column .
  • The date column must contain unique values.
  • The date column must not contain BLANKs.
  • The date column must not have any missing dates.
  • The date column must span full years. A year isn’t necessarily a calendar year (January-December).
  • The date table must be marked as a date table.

.

I hope this is helpful.

3 Likes

Hi Melissa, thank you for your reply. In a date slicer for instance, is there possibly a way to then only show the relevant dates, that is [30 December 2019, …, 6th March 2020] and everything in between? I just want to avoid the inconvenience of my user selecting dates where there is no data.

1 Like

Sure.
Just add a boolean filter to your Date table and place that in the Filter pane on Page- or Report level.
Think that should do it.

1 Like

Hi @michellepace, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @Melissa.

Just add a boolean filter to your Date table and place that in the Filter pane on Page- or Report level

I am not sure exactly what you mean. Are you saying that I need to add an additional column to my data tabe? Sorry to ask, but could you please explain in detail what you mean?

Hi @michellepace,

Your Date table should contain all attributes you need to slice and dice your data by.

Let’s create an example and copy this Date table code. Go to Power Query, New Blank Query, Paste in the M code and Invoke the function with these parameter settings.

image

Close & Apply

Drag the Date field on to the canvas and Turn it into a Slicer, the result should look like this:
image

Now drag the field “IsAfterToday” to the Report Level filter section in the Filter Pane and select false
image

The number of days available for selection and display have been reduced to only those meeting the set criteria. So if you add a Boolean filter for your “Active period” to the Date table in your Model you can do the exact same thing…

I hope this is helpful.

3 Likes

Hi @michellepace, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @michellepace, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Hi Melissa, sorry for the belated reply. Thank you very much for this. It’s wonderful :slight_smile:

1 Like