Show only data if it is between 2 dates stored in 2 different collumns.

[jgpbi]

I have a source based on contracts. I have a column for the 'Contract Begin Date' and a column for the 'Contract Expire Date'

What I want to be able to do is return records that are active which would mean their start date is before today and then end date is after today. 

I have tried the following but it still returns every date or more likely is doing nothing.

IsActive = IF(SELECTEDVALUE('CONTRACTS'[Contract Begin Date]) <= Today() && SELECTEDVALUE('CONTRACTS'[Contract Expire Date]) >= Today(), Yes, Yes)

Anythoughts on how to get this to work?

[rohit1991]

Hi @jgpbi ,

Your DAX formula isn't filtering properly because SELECTEDVALUE() only works for a single selected row in a visual. Use a measure or a calculated column instead.

Solution 1: Create a Calculated Column (Recommended for Filters):

This adds "Yes" for active contracts and "No" for expired or future contracts. You can now filter your visuals to show only "Yes".

IsActive = 
IF(
    'CONTRACTS'[Contract Begin Date] <= TODAY() && 
    'CONTRACTS'[Contract Expire Date] >= TODAY(), 
    "Yes", "No")

Solution 2: Use a Measure (For Dynamic Filtering in Visuals)

IsActiveMeasure = 
VAR CurrentDate = TODAY()
RETURN 
IF(
    MAX('CONTRACTS'[Contract Begin Date]) <= CurrentDate && 
    MAX('CONTRACTS'[Contract Expire Date]) >= CurrentDate, 
    1, 0
)

Apply this measure as a visual-level filter with IsActiveMeasure = 1.

"The goal is to turn data into information, and information into insight." – Carly Fiorina

Need Power BI help? Connect on LinkedIn.

[jgpbi]

Thank you, I figured out how to do it as an added column but I like your use as a measure better. 

Is there a way to filter out a specfic date, currently all contracts with no real end date have a date of 01/01/2099. But if I add the follwing logic to your code it isn't picking it up even though looking at the table it is stored exactly as 01/01/2099.

IsActiveMeasure =
VAR CurrentDate = TODAY()
RETURN
IF(
MAX('CONTRACTS'[Contract Begin Date]) <= CurrentDate &&
MAX('CONTRACTS'[Contract Expire Date]) >= CurrentDate &&
MAX('CONTRACTS'[Contract Expire Date]) <> 01/01/2099,
1, 0
)

Is there a way to match a specfic date?

[jgpbi · Accepted solution]

Figured that out on my own. Needed to format as DATE(2099,01,01).

IsActiveMeasure =
VAR CurrentDate = TODAY()
RETURN
IF(
MAX('CONTRACTS'[Contract Begin Date]) <= CurrentDate &&
MAX('CONTRACTS'[Contract Expire Date]) >= CurrentDate &&
MAX('CONTRACTS'[Contract Expire Date]) <>DATE(2099,01,01),
1, 0
)