Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |