Filter visuals by 2 date columns

[aey]

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

[v-ssriganesh · Accepted solution]

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.

[aey]

@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!

[tamerj1]

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 ()
)
)

[aey]

@tamerj1 I have tried using this method but it still displayed invoices that were paid in the reporting period. Do you happen to know why this might happen?

[bhanu_gautam]

@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.

[aey]

@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!