Dynamic Date Range - Weekly/Monthly - Switching and Consistant Filtering - A guide

[DavidKuhry]

Goal

We have lots of metrics that we track over trended time. Either weekly, or monthly. We want to be able to toggle a report between a weekly and monthly view, without the use of Bookmarks. It is easy to toggle the visuals between weekly and monthly with field parameters. But there's a catch.

Problem

While the visuals can easily be toggled with a field parameter, the problem is filtering the date range. I built My field parameter with 4 values, week date, month date, weeks from today, and months from today, and I added another column to it for weekly / monthly.

Here is the code for the Paramter when I'm done.

Param_DateRange_Week/Month = 
{
    ("Weekly", NAMEOF(Dates[WeekEndingDate]), 0, "Weekly"),
    ("Monthly", NAMEOF(Dates[Year-Month]), 2, "Monthly"),
    ("Weeks From Today", NAMEOF(Dates[WeeksFromToday]), 3, "Weekly"),
    ("Months From Today", NAMEOF(Dates[MonthsFromToday]), 4, "Monthly")
}

So with filtering, selecting "Weekly" will cause the matrix to have week date in the columns, and the slicer to have weeks from today as a between filter. 

This works to toggle both the Visuals and the Slicer between weekly and monthly, except that there is no way to set any default values. So no matter what you do, toggling from one to the other will cause the between values to be the absolute minimum, and absolute maximum weeks or months from today, in the date table. For me, that is over 1000 weeks. 

Filtering the slicer doesn't work, as it makes the slicer stop... Slicing. Building a measure to use as a visual filter doesn't work (at least not completely). I had started to believe this wasn't possible and that I had to continue using bookmarks to obtain this functionality. However!

Solution

Calculation Groups, and a numeric range field parameter. I've heard a lot about Calc Groups, but never used them (a regrettable delay).

I had been playing with using a Numeric Parameter range in the Slicer, and then feeding the MIN and MAX values to a measure to filter my visuals. This eventually led me to looking at the Calc Groups. 

First, my Numeric Parameter is set to have a minimum of -1000 or so, and a maximum of 52, which should be more than enough for my month and week ranges. By adding the Parameter to the between slicer, I can choose the Min and Max values. These are then passed to the Calculation Group Item, along with my Weekly/Monthly value.

Weekly Monthly CalcGroup =


VAR _Selector = SELECTEDVALUE( 'Param_DateRange_Week/Month'[Date Range Selector (W/M Group)] )
VAR _MINFromToday = MIN( 'Param_Numeric_N From Today Range'[Param_Numeric_N From Today Range] )
VAR _MAXFromToday = MAX( 'Param_Numeric_N From Today Range'[Param_Numeric_N From Today Range] )


VAR _Measure =
SWITCH( TRUE
        , _Selector = "Weekly"
            , CALCULATE( SELECTEDMEASURE()
                , KEEPFILTERS( AND( Dates[WeeksFromToday] >= _MINFromToday, Dates[WeeksFromToday] <= _MAXFromToday ) )
                )
        , _Selector = "Monthly"
            , CALCULATE( SELECTEDMEASURE()
                , KEEPFILTERS( AND( Dates[MonthsFromToday] >= _MINFromToday, Dates[MonthsFromToday] <= _MAXFromToday ) )
                )
)


RETURN _Measure

The final step is using the Calc Group in a bit different way than likely intended. I added it as a Page Level Filter. That’s about it. The Calc Group as a page filter passes any measures through the date filter and ensures I see only the ranges desired. Very effective. 

Now, admittedly, while this is working in all my scenarios so far, I am still in the building and testing phase of this concept. So, there is always the opportunity for something to blow up in my face, so to speak. But, as long as the measures are built properly and don’t yield any kind of cartesian, from like a +0 somewhere, the filtering should work.

[v-denglli-msft]

Thank you for all your sharing!