Forum Discussion
Filter visuals by 2 date columns
Hi all,
I need to filter measures in my Dashboard by 2 date columns. I have the columns "date of invoice sent" and "date of invoice paid". For my reporting, I need to set the filter so that "date of invoice sent" is my reporting period (e.g. 01.01.2025-30.04.2025) but at the sime time the "date of invoice paid" needs to either be blank or after 30.04.2025. I am trying to get this done through a date slicer if it is possible.
Is there a way to do that? I could not find any solutions while searching through the forum and internet. Would appreciate any help!!
Best Regards
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.
6 Replies
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.v-ssriganesh Thank you very much. I only used the measure in the file you provided and modified it a little bit since I had already done the "Date Periods" structure. Now i need to set the filter to "is not 1" to work but that is fine with me. Appreciate the help!
aey 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!
Hi aey
place the following measure in the filter pane of the visual, select 'Is no blank' and apply tye filter
Due Payments Filter =
COUNTROWS (
FILTER (
'MASTER',
'MASTER'[date of invoice paiddd/mm/yy]
> MAX ( 'Date Periods - Date of Invoice sent'[Date of Invoice sentdd/mm/yy] )
|| 'MASTER'[date of invoice paiddd/mm/yy] = BLANK ()
)
)
