Forum Discussion

iBot's avatar
iBot
Regular Visitor
4 years ago
Solved

how to Select data by date?

Hey guys,   i have one table (data basis) with units (apartments) and two columns with dates. All these apartments in the table are for rent. One column contains the start date where the apartment ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi iBot 

     

    Keep your Date table and Rent table disconnected. Put Date column from Date table into a slicer on the page. Then create the following measure and use it as a visual-level filter on the table visual. Set it to show items when value is 1. 

    IsEmpty = 
VAR _selectDate = MAX('Calendar'[Date])
VAR _tbl_empty = SELECTCOLUMNS(FILTER(RentData,RentData[Start Date]<=_selectDate && (RentData[End Date]>=_selectDate || ISBLANK(RentData[End Date]))),"__apartment",RentData[Apartment])
RETURN
IF(SELECTEDVALUE(RentData[Apartment]) IN _tbl_empty, 1, 0)

     

    Please download the attachment to see details. Note that I filter the result by a date rather than month as I am not sure whether an empty period would always be complete months in your data. If your requirement is more complicated, please share some sample data to help us understand it better. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi iBot 

 

Keep your Date table and Rent table disconnected. Put Date column from Date table into a slicer on the page. Then create the following measure and use it as a visual-level filter on the table visual. Set it to show items when value is 1. 

IsEmpty = 
VAR _selectDate = MAX('Calendar'[Date])
VAR _tbl_empty = SELECTCOLUMNS(FILTER(RentData,RentData[Start Date]<=_selectDate && (RentData[End Date]>=_selectDate || ISBLANK(RentData[End Date]))),"__apartment",RentData[Apartment])
RETURN
IF(SELECTEDVALUE(RentData[Apartment]) IN _tbl_empty, 1, 0)

 

Please download the attachment to see details. Note that I filter the result by a date rather than month as I am not sure whether an empty period would always be complete months in your data. If your requirement is more complicated, please share some sample data to help us understand it better. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

  • iBot's avatar
    iBot
    Regular Visitor
    4 years ago

    Thanks for the detailed solution!!