Forum Discussion
Filter visuals by 2 date columns
Hi aey,
Thank you for sharing your scenario in the community forum.I’ve reproduced your requirement in Power BI and validated the logic using sample data. The expected output is achieved successfully invoices are correctly filtered based on the selected reporting period such that:
- Only those invoices are shown where the 'Date of Invoice Sent' falls within the selected period.
- And the 'Date of Invoice Paid' is either blank or after the reporting period ends.
For your reference, I’ve attached a .pbix file demonstrating this logic end-to-end, including:
- Sample MASTER and Date Period tables.
- The DAX measure [IsValidInvoice] used for filtering.
- A working visual that dynamically updates based on the selected reporting period.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
Super Useraey Create a calculated column to determine if the "date of invoice paid" is either blank or after 30.04.2025
DAX
InvoicePaidStatus =
IF(
ISBLANK('Table'[date of invoice paid]) || 'Table'[date of invoice paid] > DATE(2025, 4, 30),
1,
0
)
Create a measure to filter the data based on the "date of invoice sent" and the calculated column:
DAX
FilteredInvoices =
CALCULATE(
COUNTROWS('Table'),
'Table'[date of invoice sent] >= DATE(2025, 1, 1) && 'Table'[date of invoice sent] <= DATE(2025, 4, 30),
'Table'[InvoicePaidStatus] = 1
)
Add a slicer to your report for the "date of invoice sent" and set the date range to your desired reporting period (e.g., 01.01.2025-30.04.2025).
Use the measure in your visualizations to ensure that only the filtered data is displayed.
bhanu_gautam thank you for the quick response!!
Is it possible to have the reporting period dynamic? Right now I use a slicer for selecting the reporting period in combination to a date slicer that lets me use a custom period for "date of invoice sent". That slicer is connected to a date table that is related to my master table. (the additional filter for "date of invoice paid" is not included in the current version)
Slicers used in dashboard:
Measure in date table:
Thanks!