Customized Filter requirement

Hi Team,

I have a unique requirement on my dashboards to filter invoice dates. So based on the current date i need to see the invoices for the previous month also.

I would like to filter, whatever the day we are in, on :

“Select Current month + Last month Invoices” > to simplify, if there a way of saying, if we are 14^th February, show invoices later than 1^st January.

so the check is for current month + Prior month

So far I have reached here

"SELECT [Invoice.Invoice Date] FROM [ALL] ORDER BY [Invoice.Invoice Date] DESC Where [Invoice.Invoice Date]
< NOWDATE()"

Now this will filter the invoice date based on current date but i want to restrict the selection with previous full month. Any help is highly appreciated

Find more posts tagged with

Comments

Dan Rosen

I recommend using the GetPromptValue function. You can create a custom expression filter on the reports in your dashboard to get the value of your selected invoice date, and then use that date to grab your desired date range (by using the DateAdd function for example). You can read up on these functions in the Birst docs but feel free to let me know if you are having issues with the syntax and I can provide some more detail

Legacy Contributor

Hi Dan,

Thank you for the suggestion. I think I used a similar approach and achieved it using the following method.

User requirement: “I want to see only the Invoices from the current date and also the invoices in the previous full month. For example, I want to see the invoices from today(17/Feb/2022 – 01/Jan/2022) and it has to be dynamic”

How we can achieve this?

Step1: Create Custom Variables under “Space Management – Manage Variables” and later we can use it as Filters in the dashboard

Custom Variable 1: CurrentDate

Code: SELECT [Time.Date] FROM [ALL] WHERE [Time.Date] = NOW

Custom Variable 2: FirstDayOfLastMonth

Code: SELECT [Time.Date] FROM [ALL] WHERE [Time.Date] = (Dateadd(Day, -((Datepart(Day ,(DateAdd( Month,-1,NOWDATE))))-1),(DateAdd( Month,-1,NOWDATE))))

Step 2: Create a Native filter in the Dashboard you want to filter based on these dates

Dashboard – Edit – Manage Filter – Advanced – More Options – Use Variables as default

Copyright © 2025 Infor. All rights reserved.