Power Query M change data type variable to data type date

[MrMike]

What is the syntax in (Power Query M / DAX) change data type variable to data type date? I tried using FORMAT but I get error message shown below.

Flag1 =

var DateRange = SELECTCOLUMNS(ALLSELECTED('date'),"Date", [Date])

return

IF(MAX('Table'[Start Date]) >= FORMAT(DateRange, "General Date") & MAX('Table'[End Date]) <= FORMAT(DateRange, "General Date"), 1, 0)

Error Message:

MdxScript(Model) (7, 4) Calculation error in measure 'Table'[Flag1]: DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[v-alq-msft]

Hi, @MrMike 

You may create a measure as below to see it works.

Flag1 =
VAR DateRange =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    IF (
        MAX ( 'Table'[Start Date] ) >= DateRange
            && MAX ( 'Table'[End Date] ) <= DateRange,
        1,
        0
    )

Best Regards

Allan

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

[MrMike]

I tried your code and got this error: 

"MdxScript(Model) (9, 9) Calculation error in measure 'Course'[IsCourseWithInDateRange]: DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."

So I tried using VALUE and I tried FORMAT and both have me syntex errors.

[mahoneypat]

SELECTCOLUMNS returns a table so you can't use it inside FORMAT.  Please show some example data and the result you are looking for.

Pat

[MrMike]

I created a sample power bi report. I don't see how to attach .pbix file to this thread.

So here is the example of data and result.

CourseNameStartDateEndDate

Math	1/1/2021	2/1/2021
Science	2/1/2021	3/1/2021
English	3/1/2021	4/1/2021
Art	4/1/2021	5/1/2021
Gym	1/1/2021	12/1/2021

Select a start and end date and all course that are with in that range should show. For example 1/1/2021 to 2/1/2021 should show Math, Science and Gym.

[v-alq-msft · Accepted solution]

Hi, @MrMike 

I am sorry for the late reply. I modify the data based on your sample data. The pbix file is attached in the end.

Table(Dates are in mm/dd/yyyy format):

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You may create a measure as below.

Visual Control = 
var _start = SELECTEDVALUE('Table'[StartDate])
var _end = SELECTEDVALUE('Table'[EndDate])
var _min = MIN('Calendar'[Date])
var _max = MAX('Calendar'[Date])
var re = 
IF(
    NOT(
        OR(
            _start>_max,
            _end<_min
        )
    ),
    1,0
)
return
re

Finally you need to put the measure in the visual level filter and use 'Date' column from 'Calendar' table to filter the result.

Best Regards

Allan

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