Use date slicer to check if date falls between two dates

[MAlSarhani]

Hello everyone!

I'm working on a report for our historical leasing records. We are trying to check the percentage of unit occupany at any given date. I have columns such as lease start date, lease end date, unit ID and unit size in SQM. What I would like to do is take in a date and check which units have been leased on that date and sum up their SQM. Then also get the sum of SQM of units that have not been leased on that date. It needs to be dynamic so the inserted date can be changed on the report.

I've come to the idea that I need to use measures for this and I need a Date table to use as a parameter by using it in the report as a slicer. I've come this far but I am confused on how I should write the measure to do it. I'm honestly not sure if its even possible.

A data sample:

Unit ID	Unit Size	Start Date	End Date
Unit 1	20	1st Oct 2015	31st Sep 2016
Unit 2	30	1st Nov 2015	31st Apr 2016
Unit 3	40	1st Jan 2017	31st Dec 2017

The result should be a total of leased area is 50 SQM if the date is set to something like 1st Dec 2015 as both Unit 1 & Unit 2 are are leased during that time. 40 SQM would be the number of the vacant area. Hope someone could help me in this!

Many thanks,

Mahmood

[Phil_Seamark · Accepted solution]

Hi @MAlSarhani

You could try a disconnected table for your slicer and use a calculated measure as a filter for your visual.

The calculated measure could look like this.  I have attached a PBIX file.

Measure as Filter = 
VAR SelectedDate = SELECTEDVALUE('Dates'[Date])
RETURN 
    IF(SelectedDate >= MIN('Table1'[Start Date]) && SelectedDate < MAX('Table1'[End Date]),1,0)

[MAlSarhani]

Hi @Phil_Seamark,

That worked perfectly, Thanks!