How to display MySQL records Month wise for last 'N' months in Bold BI

Follow the below steps to display records month wise for last 'N' months for MySQL data source.

Steps to display records month wise:

1. For the corresponding date field, create a list-based dashboard parameter under the "Custom Query" option.
   

   (SELECT MAX(`<column-name>`) AS `<name>` FROM `<table-name>` AS `<name>` LIMIT 1)

   
   Sample query: (SELECT MAX(`100000 Records`.`42f47c50-c051-4565-8573-a49848817b7f`) AS `maxdate` FROM `datasource_8be8c0a1-923d-4992-aaeb-212c433e323f`.`100000 records` AS `100000 Records` LIMIT 1)



Note: The date format should be `yyyy/MM/dd`.

2. Convert the parameter to a datetime field by creating the following expression: 
   

   CAST(@{{:Parameter1}} AS DATETIME)

   
3. Create expressions to fetch data based on the latest month and previous months.Latest month:
   IF (YEAR([Date]) = YEAR([Maxdate]) AND MONTH([Date]) = MONTH([Maxdate]), [Integer], 0)Previous month: IF (YEAR([Date]) = YEAR([Maxdate]) AND MONTH([Date]) = MONTH([Maxdate]) - 1, [Integer], 0)





Note: The year also needs to be compared to get the correct data.

4. Use the expressions to display the data in your dashboard.

Additional References

• Configuring list type dashboard parameters
• Configuring Expression fields