Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
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
amitchandak
Super User
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.

View solution in original post

2 REPLIES 2
v-rongtiep-msft
Community Support
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.

vpollymsft_0-1645679692292.jpeg

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

vpollymsft_0-1645679738878.png

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
Super User
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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.