How to Create Custom Relative Date Filters using Expressions in Bold BI
In some cases, the built-in relative date filters in Bold BI may not provide the exact filtering needed. This article will guide you through creating custom relative date filters for Year to Date, Month to Date, and Week to Date for both the current and previous years.
Custom Expressions for Relative Date Filters
To create custom relative date filters, you can use the following expressions for different databases:
MSSQL and PostgreSQL
Replace [date_col] with your date column and [price] with your measure column in the expressions below.
Current Year to Date
if([date_col]::date >= date_trunc('year', CURRENT_DATE) and [date_col]::date <= CURRENT_DATE , [price],0)
Previous Year to Date
if([date_col]::date >= DATEADD(year,-1,date_trunc('year', CURRENT_DATE)) and [date_col]::date <= DATEADD(year,-1,CURRENT_DATE) , [price],0)
Current Year Month to Date
if([date_col]::date >= date_trunc('month', CURRENT_DATE) and [date_col]::date <= CURRENT_DATE , [price],0)
Previous Year Month to Date
if([date_col]::date >= DATEADD(year,-1,date_trunc('month', CURRENT_DATE)) and [date_col]::date <= DATEADD(year,-1,CURRENT_DATE) , [price],0)
Current Year Week to Date
if([date_col]::date >= date_trunc('week',( CURRENT_DATE)::date) and [date_col]::date <= (CURRENT_DATE)::date , [price],0)
Previous Year Week to Date
if([date_col]::date >= DATEADD(year,-1,date_trunc('week', CURRENT_DATE)) and [date_col]::date <= DATEADD(year,-1,CURRENT_DATE) , [price],0)
Conclusion
By using these custom expressions, you can create more specific relative date filters in Bold BI to better suit your needs. This will allow you to compare data for the current and previous years with more precision. You can also use the Relative Date Filter option on date columns to calculate MTD and YTD and more.
Additional References
