Forum Discussion
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 has no renter. The second column contains the last date before the start of a new contract or its null if we dont have a new contract.
Now i want to select a combination of month and year on my report and i want to see a table of the aparments which were empty in the selected month-year-combination.
So far i create a date table and the month-year-combinationen but i dont understand how i could connect this with the table results.
I hope you understand my problem and can help me.
Thank you.
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.
3 Replies
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!!
Hi iBot ,
Can you share a sample dataset to have a better understanding of your query ?
