Obtaining the last month of a calendar

In this scenario, we start with a calendar and we want to obtain the last month included in it, regardless of the filters that may be applied to the data model. In our example, this calendar will include, in addition to the date field, a field with the year, month... and a text field in which the year and month of each date have been concatenated (in text format), ensuring that the result has 6 characters in length (or, in other words, ensuring that the month is represented with two digits). This can be done in the query editor by creating a custom column with the following M code.

Back in the report editor, we verify that our last month is -in the dataset that is being used- December 2019, which we can confirm in multiple ways, for example, in the data view by sorting the calendar according to the YearMonth field.

Our goal is to obtain that last month (or, more exactly, the value of the 'Calendar'[YearMonth] field through a measure.

As the 'Calendar'[YearMonth] field is a text field and is formed by the year and the month -in that order-, we can use the MAX function to get the maximum value we are looking for:

In the previous image, the measure [Last Month] has been added to a card.

The problem is that our measure [Last Month], as it is defined, will be contextualized, which means that as soon as we filter something somewhere, the returned result will not be the expected one. For example, if we take the Calendar[Year] field to the canvas in the form of a slicer and select a year, our measure will return the last month of that year.

We need to ensure that our calculation (the maximum value of the field that interests us) is performed in a modified filter context: one that ensures that the 'Calendar'[YearMonth] column includes all values. To do this, we can use the CALCULATE function by adding the ALL function as an argument. However, one mistake we can make is to apply the ALL('Calendar'[YearMonth]) expression as a filter:

As we see in the previous image, the measure [Last Month] continues to return the last month of the year we are filtering by. And this is because when the ALL('Calendar'[YearMonth]) filter is considered, the Calendar table is already being filtered by the slicer that we have taken to our report, which filters the Calendar[Year] field. What we need is to make sure that the entire Calendar table is included in our calculation, and to do this, we just need to add the name of this table as an argument to the ALL function.

As we can see, now our measure [Last Month] is returning the value we want.