Display Last Value within Range Specified by Slicer

[bkirshenbaum]

Hello!

I'm trying to build a report that shows values relative to the current date for a variety of properties in a table.

Property Name	Pre Lease %	Occupied %	Web Traffic
Site A	55%	25%	37
Site B	65%	20%	53
Site C	75%	15%	48

Example: If I set the Date Slicer to 3 months ago, I want it to show me the data that was entered nearest to February 6th (3 months ago). However, it seems I only have the ability to pull the average, sum, etc of the values from that range. These values are entered in to our database weekly, so if I set it to "Do Not Summarize", I get a ton of Site A, Site B, and Site C lines, each with their own entry.

Thank you for your help and I'm happy to provide more context if necessary!

[v-danhe-msft]

Hi @bkirshenbaum ,

Could you please post some simple sample data and your desired result to have a test if possible? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Daniel He

[bkirshenbaum]

Sorry about that!

Below is the sample data, and I am trying to filter based upon the Modified Date. There are several entries for each property throughout time, and when we filter via the slicer, we want to be able to capture a single piece of data that is closest to the relative date selected and display it in a table or matrix.

Thank you!

Property ID:Property Name	Occupied	calcOccupied	calcPreleased	Pre Leased	Weekly Traffic	Competition Rank	Modified
identity	0	0	0	0	0	0	Monday, January 22, 2018
tower	100	1	0.18	18	0	1	Monday, January 22, 2018
4th street	0	0	0	0	0	2	Monday, January 22, 2018
advenir	90	0.9	0	0	0	3	Monday, January 22, 2018
advenir	90	0.9	0	0	4	3	Monday, January 22, 2018
4th street	98	0.98	0	0	0	2	Monday, January 22, 2018
tower	100	1	0.18	18	0	1	Monday, January 22, 2018
tower	99.63	0.9963	0.2232	22.32	4	1	Monday, February 12, 2018
4th street	98	0.98	0.3	30	0	2	Monday, February 12, 2018
advenir	90	0.9	0	0	4	3	Monday, February 12, 2018
advenir	90	0.9	0	0	4	3	Monday, February 12, 2018
advenir	0	0	0	0	0	3	Monday, February 12, 2018
tower	100	1	0.3	30	7	1	Thursday, March 1, 2018
4th street	97	0.97	0.35	35	4	2	Thursday, March 1, 2018
advenir	0	0	0	0	0	3	Thursday, March 1, 2018
4th street	98	0.98	0.3	30	0	2	Thursday, March 8, 2018
advenir	0	0	0	0	0	3	Thursday, March 8, 2018
advenir	0	0	0	0	0	3	Thursday, March 8, 2018
tower	100	1	0.3	30	7	1	Thursday, March 8, 2018
4th street		0	0				Friday, March 16, 2018
4th street	98	0.98	0.32	32	5	2	Friday, March 16, 2018
4th street	98	0.98	0.5142	51.42	12	2	Friday, March 23, 2018
4th street	97	0.97	0.5516	55.16	21	2	Friday, March 30, 2018
4th street	97	0.97	0.57	57	21	2	Friday, April 6, 2018
tower	100	1	0.6	60	25	1	Friday, June 22, 2018
advenir	81	0.81	0.87	87	0	3	Friday, June 22, 2018
4th street	95	0.95	0.9	90	43	2	Tuesday, June 26, 2018
4th street	95	0.95	0.9	90	43	2	Tuesday, July 10, 2018
4th street	95	0.95	0.9	90	43	2	Tuesday, July 10, 2018
tower	100	1	0.6	60	25	1	Tuesday, July 10, 2018
tower	100	1	0.6	60	25	1	Tuesday, July 10, 2018
tower	100	1	0.75	75	12	1	Thursday, July 19, 2018
4th street	95	0.95	0.94	94	28	2	Thursday, July 19, 2018
advenir	86	0.86	0.88	88	0	3	Thursday, July 19, 2018
tower	100	1	0.7	70	12	1	Thursday, July 19, 2018
fountain	0	0	0	0	0	0	Monday, July 23, 2018
tower	100	1	0.9	90	20	1	Friday, July 27, 2018
advenir	89	0.89	0.91	91	9	3	Friday, July 27, 2018
fountain	0	0	0	0	0	0	Friday, July 27, 2018
identity	0	0	0	0	0	0	Thursday, September 13, 2018
tower	100	1	0.9	90	20	1	Thursday, September 13, 2018
4th street	95	0.95	0.98	98	28	2	Thursday, September 13, 2018
4th street	98	0.98	0	0	28	2	Thursday, September 13, 2018
advenir	89	0.89	0.91	91	9	3	Thursday, September 13, 2018
advenir	89	0.89	0.91	91	9	3	Thursday, September 13, 2018
fountain	0	0	0	0	0	0	Thursday, September 13, 2018
fountain	96	0.96	0	0	15	3	Thursday, September 13, 2018
fountain	96	0.96	0	0	15	3	Thursday, September 13, 2018
tower	100	1	0	0	10	1	Thursday, September 13, 2018
identity	0	0	0	0	0	0	Thursday, September 20, 2018
tower	100	1	0	0	10	1	Thursday, September 20, 2018
tower	100	1	0	0	0	1	Thursday, September 20, 2018
4th street	97	0.97	0.03	3	8	2	Thursday, September 20, 2018
advenir	85	0.85	0	0	9	3	Thursday, September 20, 2018
fountain	96	0.96	0	0	15	3	Thursday, September 20, 2018
fountain	96	0.96	0	0	9	3	Thursday, September 20, 2018
identity	0	0	0	0	0	0	Thursday, September 27, 2018
tower	100	1	0	0	12	2	Thursday, September 27, 2018
4th street	97	0.97	0.03	3	8	1	Thursday, September 27, 2018
advenir	85	0.85	0	0	8	3	Thursday, September 27, 2018
fountain	96	0.96	0	0	9	3	Thursday, September 27, 2018
fountain	96	0.96	0	0	9	3	Thursday, September 27, 2018
fountain	96	0.96	0	0	11	4	Thursday, September 27, 2018
4th street	98	0.98	0.05	5	8	1	Thursday, September 27, 2018
identity	0	0	0	0	0	0	Thursday, October 4, 2018
tower	100	1	0	0	12	2	Thursday, October 4, 2018
4th street	97	0.97	0.01	1	7	1	Thursday, October 4, 2018
fountain		0	0				Thursday, October 4, 2018
fountain	95.61	0.9561	0	0	11	4	Thursday, October 4, 2018
advenir	85	0.85	0	0	6	3	Thursday, October 4, 2018
tower	100	1	0	0	5	2	Thursday, October 4, 2018
identity	0	0	0.000016	0.0016	0	0	Thursday, October 11, 2018
tower	100	1	0	0	0	2	Thursday, October 11, 2018
4th street	98	0.98	0.01	1	6	1	Thursday, October 11, 2018
advenir	85	0.85	0	0	6	3	Thursday, October 11, 2018
advenir	85	0.85	0	0	3	3	Thursday, October 11, 2018
fountain	95.61	0.9561	0	0	11	4	Thursday, October 11, 2018
fountain	95.61	0.9561	0	0	6	4	Thursday, October 11, 2018
advenir	85	0.85	0	0	5	3	Thursday, October 18, 2018
fountain	95.61	0.9561	0	0	5	4	Thursday, October 18, 2018
identity	0	0	0.000016	0.0016	0	0	Thursday, October 18, 2018
4th street	98	0.98	0.01	1	3	1	Thursday, October 18, 2018
tower	100	1	0	0	0	2	Thursday, October 18, 2018
tower	100	1	0	0	0	2	Thursday, October 18, 2018
tower	99	0.99	0.0221	2.21	5	2	Thursday, October 18, 2018
identity	0	0	0.000016	0.0016	0	0	Thursday, October 25, 2018
advenir		0	0				Thursday, October 25, 2018
advenir	83.44	0.8344	0	0	8	3	Thursday, October 25, 2018
fountain	95.61	0.9561	0	0	5	4	Thursday, October 25, 2018
fountain	95.61	0.9561	0	0	5	4	Thursday, October 25, 2018
tower	99	0.99	0.03	3	5	2	Thursday, October 25, 2018
4th street	99	0.99	0.0409	4.09	4	1	Thursday, October 25, 2018
advenir	83.44	0.8344	0	0	8	3	Thursday, October 25, 2018
fountain	97	0.97	0	0	3	4	Thursday, October 25, 2018
identity	0	0	0.0032	0.32	0	0	Thursday, November 1, 2018
4th street	99	0.99	0.074	7.4	4	1	Thursday, November 1, 2018
advenir	82.7	0.827	0	0	5	3	Thursday, November 1, 2018
fountain	97	0.97	0	0	3	4	Thursday, November 1, 2018
fountain	97	0.97	0	0	3	4	Thursday, November 1, 2018
fountain	97	0.97	0	0	2	4	Thursday, November 1, 2018
tower	99	0.99	0.03	3	6	2	Thursday, November 1, 2018
tower	99	0.99	0.035	3.5	6	2	Thursday, November 1, 2018
identity	0	0	0.0064	0.64	0	0	Thursday, November 8, 2018
advenir	82.7	0.827	0	0	2	3	Thursday, November 8, 2018
fountain	95	0.95	0	0	7	4	Thursday, November 8, 2018
4th street	99	0.99	0.1	10	8	1	Thursday, November 8, 2018
tower	98.9	0.989	0.0775	7.75	5	2	Thursday, November 8, 2018
identity	0	0	0.0097	0.97	0	0	Thursday, November 15, 2018
advenir	82.78	0.8278	0	0	4	3	Thursday, November 15, 2018
tower	99	0.99	0.1	10	5	2	Thursday, November 15, 2018
advenir	82.78	0.8278	0	0	4	3	Thursday, November 15, 2018
4th street	99	0.99	0.1	10	8	1	Thursday, November 15, 2018
fountain	95	0.95	0	0	7	4	Thursday, November 15, 2018
4th street	99	0.99	0.1	10	8	1	Wednesday, November 21, 2018
tower	99	0.99	0.1	10	5	2	Wednesday, November 21, 2018
tower	99	0.99	0.1	10	5	2	Wednesday, November 21, 2018
identity	0	0	0.0097	0.97	0	0	Wednesday, November 21, 2018
identity	0	0	0.0097	0.97	0	0	Wednesday, November 21, 2018