cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MAlSarhani Frequent Visitor
Frequent Visitor

Use date slicer to check if date falls between two dates

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 IDUnit SizeStart DateEnd Date
Unit 1201st Oct 201531st Sep 2016
Unit 2301st Nov 201531st Apr 2016
Unit 3401st 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

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Use date slicer to check if date falls between two dates

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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Phil_Seamark Super Contributor
Super Contributor

Re: Use date slicer to check if date falls between two dates

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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

MAlSarhani Frequent Visitor
Frequent Visitor

Re: Use date slicer to check if date falls between two dates

Hi @Phil_Seamark,

 

That worked perfectly, Thanks!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 150 members 1,901 guests
Please welcome our newest community members: