Determine if dates between 2 date columns are in the calendar

[Anonymous]

Hi all, 

How do I determine using DAX whether the dates between 2 columns (column A and B) are within dates shown in the created calender with an If statement of yes or no for a calculated column?

Are there any solutions to this problem please?

[v-rongtiep-msft]

Hi @Anonymous ,

The created calender in your dataset is a table or a column? I have created a simple sample, please refer to my pbix file to see if it helps you.

Manage the relationship between the two table.

Then create a measure.

Measure =
VAR _mindate =
    MINX ( ALL ( 'Table' ), 'Table'[date_1] )
VAR _maxdate =
    MAXX ( ALL ( 'Table' ), 'Table'[date_2] )
RETURN
    IF (
        MAXX ( ALL ( calender ), calender[Date] ) <= _maxdate
            && MAXX ( ALL ( calender ), calender[Date] ) >= _mindate,
        "YES",
        "NO"
)

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , New columns

New column for Col 1=

var _min = Min(calendar[Date])

var _max = Max(calendar[Date])

return

if([Col1] >= _min && [Col1]<=_max , "Col 1 is in calendar dates", "No")

New column for Col 1=

var _min = Min(calendar[Date])

var _max = Max(calendar[Date])

return

if([Col2] >= _min && [Col2]<=_max , "Col 2 is in calendar dates", "No")

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.