Back in my early days, when dinosaurs roamed the earth and I was modeling data warehouses, I would build out elaborate Time Dimensions that had a multitude of flags that would allow me to use them in reporting to show specific time periods. It was a magic time full of awe and wonder.
Nowadays, I find myself always having to recreate these flags as filters because it seems very rarely do I see these beautifully built Time Dimensions… but no more.
My friend ,Vincent Baumel, tweeted about about how he had a saved data source that had all the days in the current year. I thought that was pretty cool, and over the past week or two I took his concept a step further and married it with my time dimension concept.
Currently, it boasts 27 different date flags that can be used as filters. The idea is that you would join this data source to your primary data source, using the date, and you would then have access to filter you data 27 different ways (or more if you start combining them) to make it easier to deal with time periods.
At its heart is a series of logic statements that reference the TODAY() function so that the flags all remain up to date. Back in the day, this was all done using an ETL process to update the flags in the time dimension.
YEAR([Date]) = YEAR(TODAY())-1
AND DATEPART('quarter', [Date]) = DATEPART('quarter', TODAY())
This generates a True/False boolean value, which you can add to the filters card and set true. You could also start to combine these filters, so if you wanted this year and last you you could create a calculation that was:
[Current Year] and [Prior Year]
I will continue to add dates filters to this workbook as I have time, and if you think of a good one, shoot it my way!