Calculate Last Twelve Months Using DAX

One of the more common calculations a company uses is the last twelve months, or LTM, of data. This can be tricky if your date table always has a full year of dates for the current year, which it generally should. So if today is March 3, 2019, my date table will have dates through December 31, 2019. This is usually necessary for the date intelligence functions in DAX to work properly, and companies may have data beyond today in their model. For example, budget and forecast data will generally extend through the end of the year, or at least beyond today.

However, it often is challenging when you are trying to hide these future dates for specific measures. I’ve seen solutions that use functions like LASTNONBLANK() that get the last date with sales data in it, and that can work, but depending on how your data is laid out, it can make for larger and more complex measures with multiple FILTER() functions. For a visual you can sometimes use the relative filtering feature, but that won’t change the underlying value of the measure if you reuse it in another visual or refer to it from another measure.

Marco Russo recently wrote an excellent post on hiding future dates or calculations in DAX. The concept is brilliantly simple. Just add a column to your date table that returns TRUE if the date is today or earlier, or FALSE if it is after today, then use the CALCULATETABLE() function to return just a table of dates that fall in that TRUE range of dates.

That wouldn’t work for me though exactly as it was presented. I needed to create dates that were in the previous 12 calendar months, and I was working with a Power BI Dataset, which is a Live Query, and you cannot add columns to Live Query models.

20190303 - No New Column SSAS.png


So I opted to create two measures. First, I needed to create the date logic in my dates table. I wanted the previous 12 full calendar months, not the last 365 days of data. Note that my date table is named ‘Calendar’.

LTM Dates = 
VAR EndDate =
    EOMONTH ( TODAY (), -1 )
VAR StartDate =
    EDATE ( EOMONTH ( TODAY (), -1 ), -12 ) + 1
RETURN
    IF (
        MAX ( 'Calendar'[Date] ) >= StartDate
            && MAX ( 'Calendar'[Date] ) <= EndDate,
        TRUE (),
        FALSE ()
    )

This measure has two variables:

  1. EndDate - This calculates the last day of the month for the previous month based on TODAY().

  2. StartDate - This calculates the month 12 months prior to the EndDate, then adds one day to move to the first day of the next month.

Finally the measure uses a basic IF() statement, with some AND logic. If today is March 3, 2019, it will return TRUE for the dates March 1, 2018 through February 28, 2019. For dates before March 1, 2018, and after February 28, 2019, it returns FALSE. It will do the for the entire month of March. On April 1, the LTM range becomes April 2018 - March 2019.

I could have used the AND() function instead of the double ampersand, but I use the double ampersand as I can use multiple conditions, like condition1 && condition2 && condition3, whereas AND() is limited to two conditions. By getting in the habit of using &&, I never have to remove an AND() function and redo the syntax. Side note: Use double pipes to allow multiple conditions for OR logic. Condition1 || condition2 || condition3, as OR() is also restricted to two conditions.

Now I needed to calculate sales for LTM. I already had the [Total Sales] measure below:

Total Sales = 
CALCULATE(
    SUM(Sales[Sales]),
    Sales[Type]="ACT"
) 

The measure for [Sales LTM] then is:

Sales LTM = 
CALCULATE(
    [Total Sales],
    CALCULATETABLE(
        'Calendar',
        FILTER(
            'Calendar',
            [LTM Dates] = TRUE()
        )
    )
)

You could combine my first measure with the second measure, replacing [LTM Dates] with the full measure, after tweaking the date logic in the FILTER() section a bit in this [Sales LTM2] measure.

Sales LTM2 =
VAR EndDate =
    EOMONTH ( TODAY (), -1 )
VAR StartDate =
    EDATE ( EOMONTH ( TODAY (), -1 ), -12 ) + 1
RETURN
    CALCULATE (
        [Total Sales],
        CALCULATETABLE (
            'Calendar',
            FILTER (
                'Calendar',
                'Calendar'[Date] >= StartDate
                    && 'Calendar'[Date] <= EndDate
            )
        )
    )

However, this measure is both a bit more complex, and if you wanted to have other LTM measures, such as units sold, or cost of goods over the last year, dollars purchased, etc., you’d have to repeat the date logic in each measure. If you wanted to change the LTM logic, say switch from previous 12 completed calendar months to last 365 days, or last 12 calendar months but starting with this month, you’d have to edit every measure calculating the date range. By breaking it into two parts as I’ve done above, I can edit just the [LTM Dates] measure and all other measures that use it will automatically recalculate accordingly.

Also note that unlike Marco’s solution, my date measure will not behave as a calculated column.

  1. You could not use it in an iterator function such as SUMX(), AVERAGEX(), and so on, as iterators use row context, and measures generally do not have row context. Well, iterator measures do, but they have to have row context to start with. They cannot create it out of thin air.

  2. You also cannot use measures in slicers or filters in your report. For those, you must use either a calculated column, or bring the column in through Power Query.

  3. You cannot use it as the date column in a date intelligence function, because it isn’t a column.

Calculated columns, and better yet imported columns via Power Query, can be a better choice for the above secenario, but that is not always an option if your source data is from SSAS or a Power BI Dataset where adding columns isn’t permitted.


Quickly Pad Columns in Power Query with Text

When working with data in Power Query, you often will need to pad either the beginning or ending of a column with specific text, especially if the final result will be data you export to a CSV file for uploading into a different system. Common examples include:

  • Some sort of “number” that needs to be padded with leading zero’s.

  • Labels that need to be padded with trailing spaces to ensure the cell contents are a specific length.

This can even be useful in Power BI if a report is designed to be exported by the end user to upload or otherwise use for input into a different system.

In Excel, you can do this in a worksheet cell with a formula. This will create a string 15 characters wide with trailing periods, assuming the text you want to convert to a 15 character string is in cell A1.

=LEFT(A1&REPT(".",15),15)

You can do the same in Power Query with the following formula, but this is not the easiest way.

Text.Start([Labels] & Text.Repeat(".", 15), 15)

The Text.Start() function is equivalent to Left() in Excel, and Text.Repeat() corresponds to REPT().

But there is a faster way to do this and the Power Query function name tells you exactly what it is doing.

=Text.PadEnd([Labels],15,".")

Text.PadEnd() will cause the text value, [Labels] in this example, to be padded until it is 15 characters long. I used a period so you could see the results, though you’ll most often use a space. You can see the results are the same for Excel and Power Query.

20190224 - TextPad Start.png

I changed the font in the Power Query window to monospace so you could see the text is the same length with both methods. You can change to monospace on the View tab of Power Query.

Text.PadEnd() has 3 arguments:

  1. The text you are manipulating. This can be a single filed, or a formula that generates text.

  2. The length you want your field to be. Note that if the text is longer than the padded length, Text.PadEnd() will not truncate your data to your desired length. It will return the full length of your text, but with no padding as the string is already longer than the length you set.

  3. The text to pad with, which is optional. If you leave this off, it will use spaces, ASCII code 32. If you use two or more characters, it will result in an error. It must be a single character.

You can use Text.PadStart() to pad at the beginning. It works exactly the same way as its sister function Text.PadEnd().

=Text.PadStart([Labels],15,"0")

As you can see, you only need to change from Text.PadEnd() to Text.PadStart(). Using the Excel method, you not only have to switch from the LEFT() to RIGHT() function, but change the order of the concatenation of the text in the formula, making sure not to mess up the Text.Repeat() function. The Excel method would be:

=Text.End(Text.Repeat("0", 15) & [Labels], 15)
20190224 - TextPad Start.png

In this way, Power Query is very much like Excel in that there is usually more than one way to do the same thing. I think using the Text.Pad* functions makes your code more readable, and easier to edit without having to nest multiple functions.

Intellisense in Power BI's Power Query Formula Bar

Late in 2018, the Power BI team added Intellisense to the Advanced Editor for queries in Power Query, only in Power BI. This has not yet come to Power Query in Excel.

In the February 2019 version of Power BI, that Intellisense now comes to the formula bar.

20190214 - Intellisense In M Language.png

To see this you have to enable two things:

  1. Turn on the Formula Bar in the Layout section of the View ribbon in Power Query. That should always be on by the way. Working without this is like disabling the formula bar in Excel so you cannot see the cell contents.

  2. In Power BI, go to File|Options & Settings|Options, and scroll down to the Preview Features. Check the “M Intellisense” checkbox. You’ll need to restart Power BI for this to take effect.

It still doesn’t have Intellisense in the Custom Column formula box, but we are getting closer. I suspect this feature came as a result of the recently announced “Improved Python & R Script Editor” in Power BI this month.