Create a dynamic filter or parameter to display month to date KPI

Question & Answer
, , ,
1

Hi All, I have created a KPI which shows the percentage of total sales MTD (month to date). This is month to date KPI.

I created a parameter for this KPI called “First Day of the Month” which is Start Date of Previous Month and “Last Day of the Month” which is End of previous Month and added this to the filter.

I have a “Snap Date” filter on the control line which has a date of June 30th 2014.

Is there a way to create a dynamic parameter or calculate field where if I select the snap date as May 31st, then it would basically show me the percentage of total sales KPI for the month of May?

Now because of my current parameter, it is just giving me June Data but I want this to be a dynamic KPI where if I select the previous month in my snap date, it should show me the percentage for that particular month. The data should be based on month to month basic hence I had to create this parameter but I want it to be dynamic.

2

To create a dynamic KPI that shows the percentage of total sales for the previous month based on the selected “Snap Date”, you can use a combination of calculated fields and parameters. Here’s an approach you can try:

  1. Create a Calculated Field for the Previous Month:

    • Create a new calculated field that determines the previous month based on the “Snap Date”.
    • You can use the DATEADD function to calculate the previous month:
      DATEADD('month', -1, {Snap Date})
    • This will give you the first day of the previous month.

  2. Create a Calculated Field for the Last Day of the Previous Month:

    • Create another calculated field that determines the last day of the previous month.
    • You can use the LASTDAY function to get the last day of the month:
      LASTDAY(DATEADD('month', -1, {Snap Date}))

  3. Use the Calculated Fields in the KPI:

    • In your KPI visual, use the calculated fields you created in steps 1 and 2 as the filters:
      • “First Day of the Month” = the calculated field for the first day of the previous month
      • “Last Day of the Month” = the calculated field for the last day of the previous month
    • This will ensure that the KPI shows the percentage of total sales for the previous month, based on the selected “Snap Date”.

  4. Make the KPI Dynamic:

    • By using the calculated fields, the KPI will automatically update the date range to the previous month whenever you change the “Snap Date” filter.
    • This way, you don’t need to manually update the parameter values, and the KPI will be dynamic based on the selected “Snap Date”.

Here’s an example of how the calculated fields might look:

# Previous Month Start Date
DATEADD('month', -1, {Snap Date})

# Previous Month End Date
LASTDAY(DATEADD('month', -1, {Snap Date}))

You can then use these calculated fields in your KPI visual, and the percentage of total sales will be displayed for the previous month based on the selected “Snap Date”.

3

Hi. I dont see DATEADD and LASTDAY functions in Quicksight. Are there alternative functions?

4

Hi @Rus, here is the syntax:

addDateTime(-1, ‘DD’, addDateTime(1, ‘MM’, truncDate(“MM”, now())))

Calculation Reference:

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

1 Like