Filtering Data using Dynamic Ranges

DAX contains some powerful features for filtering data on forms and reports that are easily accessible to end-users, however it’s more powerful than most understand. There’s a common misunderstanding that data on forms and reports can only be filtered by static values, but there’s a little known framework that allows the use of Excel-like functions that calculate the filter value.

As an example, say that a company has a policy to check on open purchase orders over 60 days old at the end of every month. Without the use of a dynamic date range, users would have to manually run the Open Purchase Orders report every month and change the date range each time. A simpler and more automated approach would be to setup the report to be sent to the user by email every month and set the date range as “(greaterThanDate(-60))”. This function will dynamically calculate a date range of 60 days prior to the report execution and greater.

Even better, skip the report and apply the logic above to a query used by a List Page and/or Cue.

Here’s a comprehensive list of date functions:

This framework does not just address dates, it also can calculate the current user ID (currentUserId()) and employee ID (currentEmployeeId()). To see a full list of the functions available, reference the SysQueryRangeUtil class in the AOT.

If you have a need that isn’t addressed through the standard functions, it’s fairly easy to add new ones. They can also be used in conjunction with record level security to expand the limited possibilities that the framework allows. I will go into more detail on this in a future post.