Han Solo’s Smugglers Guide to Power BI Dynamic Data Filtering using Dates

I needed to make calculations in Power BI based on changes in the database over time. These same calculations are also proving useful for making the same types of calculations concerning file system changes, physical partition changes, SAP performance evaluation and many other similar changes that we would want to track for data analysis and forecasting.

First, I needed a Power BI DAX Measure to calculate the most recent date selected in a multi-date slicer. In this case my data source is a Folder. There is a spreadsheet written to the folder each month, with a naming convention such that I can capture the date for each data point when being loaded into my Power BI model. I can cover the details for this part of the overall process in a later post.

To select the most current date, and a previous date, for the desired calculations I used a combination of the VAR, CALCULATE, MAX and FILTER functions. Once I have these dates, I can then perform my change over time analysis for my various data points. So, for this post, let’s look at the steps to determine the desired dates for investigation. I started by getting the most recent date selected in the Date Slicer. The Power BI DAX formula I use to get the most recent selected date follows.

Most Recent Selected Date =

VAR _ChangeDate =

ALLSELECTED ( 'Change'[Date])

VAR _MostRecentDate =

CALCULATE (

MAX ( 'Change'[Date]), _ChangeDate)

RETURN

_MostRecentDate

This first formula is straight forward and references a table named "Change" that tracks changes over time. The table, “Change”, has a column named "Date" that records the date of each data point. Then I use a variable named ChangeDate that contains specific dates that have been selected in the slicer. Here I determine the most recent selected date present in the "Change" table from the dates provided in the ChangeDate variable.

Now for the more interesting date selections, like last month, last year or next month, next year. As with getting the most recent date selected in the Date Slicer, getting the previous month was only a little more complex as you can see:

1 Month Before the Selected Date =

VAR _MostRecentDate =

CALCULATE ( MAX ( 'Change'[Date] ), ALLSELECTED ( 'Change' ) )

RETURN

CALCULATE (

MAX ( 'Change'[Date] ),

FILTER ( ALL ( 'Change' ), 'Change'[Date] < _MostRecentDate )

)

I added a FILTER utilizing 'Change'[Date] < MostRecentDate: this filters the table to only include dates before the MostRecentDate (latest selected date in the multi-date slicer that was identified earlier). The code filters the table to exclude the latest date and then finds the maximum date within that filtered set. Therefore, I retrieve the date for the preceding month based on the dates present in the filtered "Change" table. BTW, I will have more concerning using the FILTER with the AND statement in another posts; i.e., I discovered a way to use AND for more than just two variables, which I found useful.

Now I need to determine a date in the past, or future, that I want to use for my data analysis. Consider the following example (Measure) used to determine the date one year prior to the most recent date selected in the Date Slicer.

12 Months Before the Selected Date =

VAR _MostRecentDate =

CALCULATE(MAX('Change'[Date]), ALLSELECTED('Change'))

RETURN

VAR TwelveMonthsBefore = EOMONTH(MostRecentDate, -12)

RETURN

CALCULATE(

MAX('Change'[Date]),

FILTER(ALL('Change'), 'Change'[Date] < _TwelveMonthsBefore)

)

I added the EOMONTH function, which accepts two arguments: my most recent date selected in the Date Slicer; and the number of months to move forward (positive) or backward (negative). In this example I need to know the date from one year before the most recent selected date. Now the filter condition in the FILTER function uses the _TwelveMonthsBefore variable, which in this example is 12 months (1 year) in the past.

Determining a variety of dates as selected from a multi-date slicer proved to be beneficially as the basis for many other calculations. In the future I will post more about how these date values were utilized to perform data analysis and reporting. I will also post more about some of the more interesting formulas I have had to utilize to perform various SAP performance analysis and reporting.