Hi ,
I have a date filter . I want to have the option of filtering the metrics based on the date.
The issue I have is I also want to show MOM metrics.
However If I am selecting the option to show April 1st to April 30 th, the March data will be filtered out . Is there any option to just previous month for MOM metrics ?
The first row has all the KPIs .
I also have a table which shows the metrics based on the date selected.
Hello @liya101, in order to ensure that MoM visuals have 2 months of data available, you are going to want to exclude those visuals from filtering directly based on the user selection, and write a calculated field to guarantee 2 months of values will return.
If your start date and end date controls are linked to parameters, we can use those to determine which dates the MoM KPI should display. I’ll write some logic below:
ifelse(
truncDate('MM', ${StartDate}) = truncDate('MM' ${EndDate})
AND truncDate('MM', addDateTime(-1, 'MM', ${StartDate})) <= truncDate('MM', {Date Field})
AND truncDate('MM', ${EndDate}) >= truncDate('MM', {Date Field}), {Date Field},
truncDate('MM', ${StartDate}) <> truncDate('MM' ${EndDate})
AND truncDate('MM', addDateTime(-1, 'MM', ${EndDate})) <= truncDate('MM', {Date Field})
AND truncDate('MM', ${EndDate}) >= truncDate('MM', {Date Field}), {Date Field},
NULL
)
The first check will determine if the 2 date parameters are in the same month. If that is the case, we will convert the Start Date parameter to the month previous and compare the date field to the month previous and the month selected. Otherwise, if the 2 date parameters are not the same month, it will compare your date field to the month prior to the End Date and the End Date month.
This should always provide you with 2 months worth of dates in your MoM visuals. I will mark this as a solution, but let me know if you have any remaining questions.