Count of Trailing Twelve Months - On Filter

Question & Answer
1

Every month column in pivot should show the count of trailing twelve months even applying filter range of 4 months.

Example:

image
image1565×174 5.56 KB

2

The way I can think of to achieve this requires to limit the number of months in your range (it is 4 months in your example).

Here I assume your dataset has a date/time field called {datetime} and the field you want to do the distinct count on called {field}

  1. Add two date parameters called ${startDate} and ${endDate}. These will be used to choose the start month and end month (Sep’21 and Dec’21 in your example). Add date picker controls for these parameters and choose any day within your desired start and end month (the day itself does not matter since we will only check the month)

  2. Add a number of calculated fields as follows:

Add a calculated filed named {end - 0} with the following definition:
distinctCountOver(ifelse(datediff({datetime}, addDateTime(-0, 'MM', ${endDate}), 'MM') <= 11, {field}, null), [], PRE_FILTER)

Add a calculated filed named {end - 1} with the following definition:
distinctCountOver(ifelse(datediff({datetime}, addDateTime(-1, 'MM', ${endDate}), 'MM') <= 11, {field}, null), [], PRE_FILTER)

Add a calculated filed named {end - 2} with the following definition:
distinctCountOver(ifelse(datediff({datetime}, addDateTime(-2, 'MM', ${endDate}), 'MM') <= 11, {field}, null), [], PRE_FILTER)

Add a calculated filed named {end - 3} with the following definition:
distinctCountOver(ifelse(datediff({datetime}, addDateTime(-3, 'MM', ${endDate}), 'MM') <= 11, {field}, null), [], PRE_FILTER)

  1. Add another calculated field called {endDate month difference} with the following definition:
dateDiff(truncDate('MM', datetime), ${endDate}, 'MM')
  1. Add another calculated field named Monthly Distinct Count with the following definition:
min(
  ifelse(
    {endDate month difference}=0, {end - 0},
    {endDate month difference}=1, {end - 1},
    {endDate month difference}=2, {end - 2},
    {endDate month difference}=3, {end - 3},
    null
  )
)

  1. Add a pivot table and add the {datetime} field under the Columns field well. Change its aggregate to MONTH.

  2. Add the Monthly Distinct Count field to the Values field well of the pivot table.

  3. Add a filter to the pivot table on {datetime} between the parameters ${startDate} and ${endDate} (inclusive).

This will give you a range of 4 months maximum. If you wish to have a bigger range, then you simply need to add more fields in step 2 and reference them in the Monthly Distinct Count field