For the sake of uniformity in a bar chart, we may need to filter out dates in the latest partial week, month, quarter or year. That is what Parinita asked me about a filter to do just that.
How to design a filter for it? Especially, we need the design to take into consideration of dynamic date range which can be changed by the user.
Here we will show two approaches to it. They differentiate themselves by the order of filtering. Note that we will use a parameter Date Grain to denote the selected date period. A grain can be any of day, week, month, quarter and year. We will use the superstore data set to create examples.
1. LOD Approach
This is a pre filter approach. It will remove the data rows in the partial period before calculations. With LOD calculations, we must add the date range filter to the context.
Here is the Date by Grain:
First we get the max date of the data set
Then we have the filter as follows. Select true to remove the partial period.
2. Table Calc Approach
This is a post filter approach. It will remove the calculation results belonging to the partial period. This post filter is always second to date range filter which doesn't have to be in context.
For Date Grain, we will need to use the aggregate form:
Same as above, we need to first calculate the Max Date of the data set.
Thus we use Window_Max() to calculate it which is available everywhere. Note that we need to set it up to compute along Date by Grain.
Postscript
The LOD approach seems a little simpler. However we have to be very careful about the context. It may go wrong if we don't set the context filter correctly.
The Table Calc approach will work regardless of context. The table calculations require setting up along the date dimension carefully.
In most cases, there is a solution via LOD and also an equivalent solution via table calculations. Pick the one that you feel most comfortable about.
A demo workbook can be downloaded here.
Feel free to leave comments below or contact me at twitter: @aleksoft
Add a comment