Previously in How to Date with SAC… (that’s if you read my previous blog), I took you through how to create Thresholds based on date selections. This time we are going to look at how to do Complex Dynamic Date Filtering (my terminology).

The concept behind this is blog is show you how you can report month-to-date data based on your selected date. Sounds simple I know but there always gotcha’s in everything we do, so let me explain.

To demonstrate how this works I’m going to use an example that I had to implement for one of our clients. The requirement was for a Month to Date form of Date filtering but with a caveat that we only show until the end of the previous week. There is always and unless and the unless in this case is where we are in a new month and the week hasn’t completed in this month, in this case we want to show the previous month.

To illustrate what this looks like I will use the below calendar:

In the above month. If we are looking at the report from the perspective of Tuesday 8^th November, the report would bring back the current month up to Sunday 6^th November (End of previous week) based on Sunday being the last day of the week.

However, if we were looking from Sunday 6^th November the report would bring back the entire previous month (October) as the previous week ends in the previous month. Sound a bit crazy, stick with me it will all become clear at the end.

So how do we achieve this?

Logically we would create an if statement but due to the complex and dynamic nature of this kind of filtering there isn’t a simple way of doing this. However, what we can do is use advanced filtering.

Advanced filtering allows us create filters based on multiple dimension members, however by default it does not provide IF/ELSE functionality, it only does AND/OR. To get round this we need to create a flag and combine this with AND statements and by doing this we can effectively create an if statement.

To do this we need to create what I am calling a “Date Flag” by following the process below.

The first step in this process is to create a “Count Days” calculation:

This effectively gives us a measure that provides the number of days in a given selection.

Once we have the count days calculation we need to create two measure-based dimensions. One based on number of days in a week and the other the number of days in a month. The reason that we create a dimension rather than a measure is that we want to keep this to a static number based on a selection.

So with this in mind lets create “Days in Month”:

For the measure we use the count days calculation we previously created and we check the “Use Measure Values as dimension members”.

Lastly we add a Filter Context and for this we will use our Date and select by Range

In the Set Date Range pop up window, we want to setup a Current Date Input Control as shown below:

Now select the Granularity as Day as shown below:

Next under the range we change the Granularity as Current Month as shown below:

We now have our Days in Month dimension in place and now we create our Days in Week dimension in much the same was as Days in Month. The main difference is that instead of the Current Month Granularity selection, we select Week and toggle the Include range up to Current Period:

By doing the above, this means that when we select a particular Date we have two dimensions that come back with two numbers. One for number of days in month (up to current selected date) and another with number of days in the current week (up to current selected date).

Next, we want to create some Dimension To Measure calculations. The reason for this is that we need the “value” in our Dimensions to be a numeric so that we can create a calculation in our “Date Flag”

To do this we create a Dimension to Measure based on Days in Month as shown below:

Keeping the Aggregation operation as SUM we do the same for Days in Week as shown below:

With this setup we can now create a “Flag Measure”. (not quite the “Date Flag” more on this in a bit) and for this we create a calculated measure with the below formula:

The logic behind this is that if more days have elapsed in the current month than there are days in the current week then set the measure to 1 else 0.

The next step is to create our “Date Flag” Dimension. The main reason for creating a Dimension for this is that we can’t use measures in Advanced Filters.

This is very simply a Measure-Based Dimension on the “Flag Measure” we have just created:

Now we can set up our Advanced Filter as shown below:

Let’s break the above Advanced filter down to explain what is happening here.

We have two AND conditions under an OR. Within each AND we include the Date Flag. We create the first as Equals to 1, (note that depending on when the Current Date selection is only 1 or 0 will be available to select not both). As this is looking at the Current month we want to add in AND Date is the Current Month. Lastly, in order to have it select only up to the end of previous week we add in an AND Exclude:

And set the date to be Current week.

This first condition basically means that if the Date Flag is 1 we include everything up to the end of previous week within the current month as we have enough days.

The second condition we are looking at Date Flag Does not equal 1 and then Date is previous Month. (using an offset on month) as we do not have enough days.

In order to show this working, I have created a simple Table only showing Dates that I applied the Advanced Filter to.

Using the example I talked about at the start, if we select Tuesday 8^th November in our Current Date:

Then we see the following in our Table:

Because there are more days in the Month (8) than there are in the current Week (2) the Date Flag will be set to 1 and the first condition in the Advanced Filter becomes active and we see the dates for the previous week.

If we now select Sunday 6^th November in our Current Date control we get the following:

We can see that as there are more days in the current week (7) than there are in the month (6) so the Date Flag becomes 0 and therefore the second condition is made true and we show the previous month’s data.

Now that we have the logic in place, this gives us a foundation to make changes to the various components to allow for different data to be returned based the date selected.  For example, if we are looking at October 2022 and we select Monday 3rd:

The following data in the table below would be returned as we technically have a completed week.

However, we might want to show the previous week in the current month if it’s just going to show the weekend and we can get round this by changing the “Flag Measure” calculation to the following:

Instead of Greater than 0, we have Greater than 2. Which effectively means we need at least 3 days in the previous week in order to include it for the current month. So now when we select 3^rd October 2022 we get the following:

There are a number of other scenarios we can satisfy by making changes to the various components which will return different results. Why not have a play around and see what you can do with it?