Forum Discussion

MrMike's avatar
MrMike
Helper II
5 years ago
Solved

Power Query M change data type variable to data type date

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(ALLSELE...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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's avatar
MrMike
Helper II
5 years ago

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

Math1/1/20212/1/2021
Science2/1/20213/1/2021
English3/1/20214/1/2021
Art4/1/20215/1/2021
Gym1/1/202112/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's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.