Last 12 Months from Selected Date (Trailing 12 Months TTM)

I often have clients that require their Power BI reports to include a “Trailing Twelve Months” view of their data. In Power BI terms, if the user selects a date of 9/1/2019 in a slicer, they should see a chart showing a date range of 9/1/2018 through 9/1/2019. Confusing, right? Take a look at this demo below:

Moving forward, we’ll refer to “Trailing Twelve Months” as TTM. You might be able to see why achieving a chart showing a TTM view is more difficult than normal. That’s because, by default, Power BI will filter your visual down to what you have selected in your slicer (or other visuals on the page) and only show you the data that fits all of the filters. In our example above, we are selecting one month and showing 13 (12 trailing months and the selected month). So how do we do this? The answer is a disconnected date table.

We will simply set up a table of date that does not have any relationships to our main table of data (referred to as Master). Therefore, clicking on a single date in our disconnected date table slicer will not affect a chart with data from the Master table. Instead, we will write a special measure to handle the filtering for us.

The first step to achieving such a visual is to set up the disconnected date table. For my data, I will simply click Modelling -> New Table and enter in the following code:

Date = CALENDAR(MIN(Master[OrderDate]),MAX(Master[OrderDate]))

This creates a new table with a single column named “Date” that starts with our minimum order date from the Master table and ends on the date of the maximum order date. The data I am using comes from a sample database with order dates from 2010 through 2014. Since we want to view our data on a month level instead of a day level, let’s create a calculated column on our Date table called “End of Month Date” with the following code.

End of Month Date = EOMONTH(‘Date'[Date],0)

This basically adds a column to our calculated table that turns the individual date into the date at the end of its respective month. Here is an example of what this table looks like after finishing the previous two steps:

Let’s add a couple visuals to set up our example. First, create a slicer out of the End of Month Date column from out Date table. Then create a bar chart using End of Month Date as the Axis and nothing in the value well. It should look like the picture below. Notice that the bar chart is still blank.

To complete our demo, simply create a new measure with this formula:

TTM_Sales =

VAR CurrentDate = SELECTEDVALUE(‘Date'[End of Month Date],MAX(‘Date'[End of Month Date]))

VAR PreviousDate = CurrentDate – 365

RETURN

CALCULATE(

    SUM(Master[SalesAmount]),

    FILTER(

        Master,

        Master[End of Month Date] >= PreviousDate && Master[End of Month Date] <= CurrentDate

    )

)

The first variable, “CurrentDate”, checks if a selection is made on the Date slicer. If not, we take the maximum date in our slicer. The “PreviousDate” variable simply subtracts 365 days from CurrentDate. Finally, we return the sum of our SalesAmount but we specifically filter our Master table down to only include data between our PreviousDate and CurrentDate interval. This effectively will only return data for a 366 day period (or 13 months if you really dig into it since 13 “End of Month Dates” will be included in the range.

And with that, you have a fully functioning TTM! I’ve added a card with a dynamic measure to tell me which months are showing in the visual.

I hope you enjoyed this trick! The key is to handle the filtering of the visual through a measure instead of relationships. If you liked it, make sure to subscribe to the blog and BI Elite YouTube channel!