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.