cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

Determine if dates between 2 date columns are in the calendar

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?

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Community Support

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.

Super User

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