For Power BI version 2.76.5678.782 (December 2019).

When viewing business reports, we often want to see data in the last quarter, last two quarters, etc. To that end we can create a table that contains only the last few quarters and feed it to a slicer. Let’s look at how to create a table of last four quarters in Power Query.

Let’s start with noting you need a YM sort column in your Calendar table.

If you don’t have YM sort column in your Calendar, simply add it with Add Column > Custom Column.

Enter the formula [Year]*12 + [Month] and confirm with OK.

We can now start our query for finding the last four quarters. We follow the steps bellow.

Start by referencing Calendar.

Remove all columns except Date, Year, Quarter and YM sort with right click, Remove Other Columns.

Remove duplicates with right click, Remove Duplicates.

Extract last two digits from Year column. Select Transform > Extract Last Characters and type in 2.

Sort rows descending by YM sort column.

Merge columns with selecting Quarter and Year (in that order) and right clicking Merge Columns.

Filter Date column to dates that are older than the current date. Right click on Date > Date Filters > Before.

Select is before and Today on the drop-down menu.

Dates are now filtered.

Finally, filter Keep Rows > Keep Top Rows.

Enter 4. The final table now looks like this.

We can now load the table to the model, feed it to slicer and quickly observe our data in the last four quarters.

Shortcut: use the code

Use the code bellow and paste it to Advanced Editor to get the same result.

let

Source = Calendar,

#”Removed Other Columns” = Table.SelectColumns(Source,{“Date”,”Year”, “Quarter”, “YM sort”}),

#”Removed Duplicates” = Table.Distinct(#”Removed Other Columns”, {“Year”, “Quarter”}),

#”Extracted Last Characters” = Table.TransformColumns(#”Removed Duplicates”, {{“Year”, each Text.End(Text.From(_, “sl-SI”), 2), type text}}),

#”Sorted Rows” = Table.Sort(#”Extracted Last Characters”,{{“YM sort”, Order.Descending}}),

#”Merged Columns” = Table.CombineColumns(#”Sorted Rows”,{“Quarter”, “Year”},Combiner.CombineTextByDelimiter(“-“, QuoteStyle.None),”Quarter.1”),

#”Renamed Columns” = Table.RenameColumns(#”Merged Columns”,{{“Quarter.1”, “Quarter”}}),

#”Filtered Rows” = Table.SelectRows(#”Renamed Columns”, each [Date] < Date.From(DateTime.LocalNow())),

Custom1 = #”Filtered Rows”,

#”Kept First Rows” = Table.FirstN(Custom1,4)

in

#”Kept First Rows”