DAX Last n dates filter-CodePudding

I am wanting to apply a filter to a date column (called 'period_control_date') to retrieve the last 6 applicable dates (from the Fact table called 'payroll').

Is there a DAX formula to filter for the last n observations?

My data model is very simple:

Here is an example pivot table that I have manually filtered down to the last 6 dates:

The pay frequency could be SM for semi-monthly or BW for bi-weekly. I'm hoping there is some formula that would just work with the other filters that are applied.

I currently don't have dates table in the data model, but could add one if that is needed.

CodePudding user response：

A simple measure like

MyDateFilter:=MAX(Table1[Date])

Then, in the Pivot Table, set the Values filter to Top 6 by MyDateFilter.

You can also achieve this without any DAX, simply by dragging the Date field into your Pivot a second time, setting its aggregation to 'max' and using 'Max of Date' in the Values filter.