Dynamic Dates and Calendars

A common requirement when building a dashboard is the ability to set a value (typically a date or time value) dynamically and use that value as a filter for a dashboard, or part of a dashboard.

Lets start with a simple dataset; as always links to download the sample data and finished pbix file will be available at the end of the post.

The first step to achieving this is a calendar table, or dimension. There are a number of sources out there for generating or downloading a calendar table. I was drawn to this method as it involves generating a date table dynamically within the Power BI model using DAX.

Start by choosing Add Table from the Modelling tab and providing the following in the formula bar:

Calendar =
CALENDAR ( "01-01-2016", "31-12-2016" )

The invoked CALENDAR function will generate a range of dates, based on the start and end dates supplied as arguments.

Then, using the ADDCOLUMNS function, extend the created table to include whatever date derivations are required.

Calendar =
ADDCOLUMNS
(
CALENDAR ( "01-01-2016", "31-12-2016" ),
"DateAsInteger", FORMAT ([Date], "YYYYMMDD")
)

Finally, extend the function to provide columns that identifies the dynamic date(s) required to make the dashboard work as required.

Calendar =
ADDCOLUMNS
(
CALENDAR ( "01-01-2016", "31-12-2016" ),
"DateAsInteger", FORMAT ([Date], "YYYYMMDD"),
"Yesterday", if([Date] = today()-1,1,0),
 "LastSevenDays", if([Date] >= TODAY()-7 ,
if([Date]<= TODAY(),1,0))
)

The last two items add flags to the Calendar table based on the TODAY() function. The flags will update dynamically whenever the dataset is refreshed.

To leverage this very useful table first create a relationship between it and the imported Sales table. From the Relationship view accessed from the left toolbar drag Date from the Calendar table over Sale Date in the Sales table to create the relationship. Power BI should detect a relationship which looks like when inspected.

The relationship type detected should Many to One (*:1)

Back in visualisation mode, create a simple chart by dragging Sales Amount and Date on the design canvas. Set the Axis to just use date...

A rather cluttered chart will emerge as it is rendering a data point for every date in the dataset. To set the chart to just show the last seven days drag LastSevenDays from the Calendar table into the Visual Level Filter section of the graph. Set the filter to only show values where LasdtSevenDays is 1.

This technique can be employed at visual, report or dashboard level.

Samples:

To ensure this sample works regardless of when it is viewed, LastSevenDays HardCoded which provides literal dates for the seven days the chart discloses has been added. It is this value which is used to filter the visual.

Also provided are a number of common date derivations that expose common values used in a calendar. To ensure items like Day sort correctly there are a number of numerics included, Day Number, Week Number, etc. These are used as sorts for Day, Week etc

Download the sample workbook here. The .pbix file, including the DAX generated calendar is here.