Forum Discussion
how to Select data by date?
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.
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.
Thanks for the detailed solution!!