How to set Month filter to current month?

Question

Hi,

I want to give users the possibility to change month for displaying data, in order to let them see past data.

I can set the month as a fixed value but in this way it doesn't update while time passes.

I can also set the month by using an expression filter (YEAR(CURRENT_DATE) = YEAR AND MONTH(CURRENT_DATE) = "HCM - Workforce Core"."Time"."Month Number") but in this way the Month filter gives only the current month as valueset and user cannot select any past value.

How can I have the complete valueset of months while automatically update the default value when a new month arrives?

Thank you

Caroline Blanchard · Accepted Answer

Hello. I would recommend binding a parameter to your month filter!

Start by going to the parameters tab and creating a new parameter:

Next, toggle on multi select button, select the column value from the available values, and select month from the drop down. In the initial value drop down, select Logical SQL Query. Input the below query:

SELECT
 "HCM - Workforce Core"."Time"."Month"

FROM "HCM - Workforce Core"

WHERE
 ("HCM - Workforce Core"."Time"."Trailing Month Number" = 0)

ORDER BY 1 ASC NULLS LAST

From here, you will bind the parameter to your dashboard or canvas filter. Add the month field as a filter. Select the parameter (x) button and select your new current month parameter.

Now you will see the current month which will dynamically update as time goes on and the end user will be able to change the selection.

Let me know if you have any questions, hopefully this works for you!