MTD, QTD, & YTD Filters

#1

I have a 3 table visualizations (see below images): Mth-To-date, Qtr-to-date, & Yr-to-date with a date hierarchy slicer. By default the tables are filtered to display date ranges matching the table names (MTD, QTD, YTD).

I added two Value filters connected to the Date Hierarchy. One filter is modified to only show Year and the other modified for Month.

Dundas%20Custom%20Filtering
Dundas Custom Filtering.jpg1425×291 89.4 KB

What i want is for the tables to filter MTD, QTD, and YTD based on the month and year selected on the filters. I just don’t know how to link the metric sets/table’s MTD/QTD/YTD parameter value to the Year and Month filter parameter Value. I tried an alternative solution found here but slicer comparisons don’t work since I also need to add PoP measures which aren’t allowed.

As always any guidance is appreciated.

#2

Hi Everardo,

Thank you for reaching out Dundas Community.

In your use case we have two options to achieve the requirement

• You can use Calendar range filter instead of value filter so you can easily connect the metric set with calendar range filter. If you want select April 2020, you may select calendar range as April 1, 2020, to April 30, 2020. It will filter the April 2020 data in the dashboard. Please see the link for your reference
https://www.dundas.com/Support/learning/documentation/create-view-content/filters/adding-filters#h2-types-of-filters

image

• If you want to use value filter you may need to create separate year and month hierarchies in cube level. You can use calculated elements functionality to create year and month hierarchies. Add these two as slicers and use this to connect the value filter. Please keep original date hierarchy to create PoP analysis

You need to perform POP analysis in metric set level, and you need to add the metric set into Dashboard. Please see the documentation link for your reference

https://www.dundas.com/Support/learning/documentation/analyze-data/add-a-period-over-period-comparison

Thanks ,

1 Like
#3

Thank you @Denil. I am overthinking this and going to use the calendar range filter. Thanks for your suggestion

#4

Everardo, I recently did this same thing and was able to get MTD, QTD and YTD in one metric set. I had a value filter on a date that only showed the month (ex:January, 2022) that was visible to the users. I created a slicer for each period I needed, including previous time periods, always checking the box Automatically Add Parameter Controls. This was a way to “fake” the POP measure. This resulted in a separate calendar filter for each of these slicers. Then I had script that ran on the Parameter Value Changed event in the single month year value filter that set all of these calendar filters to the appropriate time range based on the user’s selection. Once I had it all working, I hid all of the calendar filters except for the single month year value.